Monday, April 9, 2012

Sql Authentication and Windows impersonation


Recently i was working on a BCS solution in SharePoint 2010 and realized a scenario where i have to authenticate SQL Server database with a windows user account for all authenticated users using a connection string.
after trying a good four hours on topic i found there is no easy way where you can provide a service windows account as an account for sql authentication in connection string. so i used a sql server account for such impersonation in connection string with username and password.
 
but the question was still in my mind for is it the way designed or i missed something which i should know. Then i thought to explore and i found it true. WHY?, next question you might put up and the reasons are below
  • When integrated security is "true" or "SSPI" What is SSPI? the basic assumption it uses current thread's identity for authentication with SQL Server, so impersonate the current thread to achieve the scenario.
  • With windows impersonation in sql server connection string might trouble you incase your enterprise wide password maintenance policy (i.e. change password every quarter).
  • Risk of identoty theft i.e. you need to know the password of windows user (although service dummy account can be used to overcome this factor but it again becomes overhead for It support team).
Further Readings:

No comments: