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.
- 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).
