Switch from Windows Integrated Authentication to SQL user for database connection

News Knowledge Base
1

How to switch from Windows Authentication to SQL login for connecting to SQL database?

Before switching from Windows Integrated authentication to SQL authentication (or viceversa), consider the following:

  • Validate how the current instance is working. Confirm what is the identity that is running the ApplicationPools in IIS.
  • Check the connection strings to confirm if it is using the account from IIS to connect to SQL (Windows Auth) or if the credentials are specified in the configuration files (SQL user).


Regardless of the login type, make sure to verify:

  • The type of authentication allowed in SQL as described here.
  • The user must have certain roles as described here.

Then, switch the authentication type by modifying all the connection strings described here: Knowledge: Locations Of The Database Connection Strings (salesforce-sites.com). For example:

  • This is a connection string with Windows Authentication: The parameter “Integrated Security=True" will use the identity from the IIS application pool to authenticate against SQL server using Windows Authentication.
  • This is a connection string with SQL authentication: Specify username and password for SQL authentication.

For more details, check: Connection String Syntax - ADO.NET | Microsoft Learn.