Correct way of configuring remote users to connect to Microsoft SQL Server 2012

Question

I have 2 Servers, a web server and a database server. Web server runs a number of .net sites that need to connect to the database server.

Currently, I have a configuration string for each application, that specifies a database instance, user name and password, and then have to add in these users onto the Database, and give them the necessary permissions.

Locally however, I can use the integrated Ap Pool Identity. This means that when the code is deployed config strings need to be changed, and a number of db users need to be managed, along with relevent passwords.

To me this doesn't seem the best way. I have read about Managed Service Accounts, but the last thing I read implied that they are not officially supported yet (this was in 2014).

So which is the 'correct' way of connecting my app pool users from one server to the database on another?

Answer 1

Managed service accounts are supported in SQL server 2012 onwards to run it's services.

There is no 'correct' way but managed service accounts on the application server are normal domain users as the SQL Server is concerned so as long as you can use them for the application they are a nice option. You might have to type the name in (domain\appuser$) as older versions of SSMS don't know the object type but you can add them manually

To minimize the configuration changes you can do the following:

• Use client aliases on your application servers in the connection strings, for each application create an alias for the database (use cliconfg.exe) so that AppA connects to the the database server AppAdb. The alias on test will then point to the testdb server, prod to prod. Then you dont need to change the hostname in the connection string and you dont need to worry about the production application server suddenly starting to talk to the test database.
• Use contained databases to minimize the need to create users on the database servers, you can then migrate databases between servers without having to remembering to add the users.
• Use integrated App Pool Identity's and windows authentication using domain users, managed service accounts when possible, then you are not storing password in .config files, gMSA will allow you to have the same user on different servers.
• If you have to use SQL Server authentication encrypt the passwords.