SQL Connection String Using a Domain User?

Question

Previously for all our asp.net applications we have been using a sysadmin user within SQL Server to connect and add/update/delete/get data. Our SQL Admin wants to delete that account and create a Domain Account so we can use that account within our .net applications.

My current connection string is:

name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient"

What would the connection string be for using a domain account?

I tried:

name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=domain\user;Password=password" providerName="System.Data.SqlClient"

and it does not work.

Is there a different way to connect to SQL Server using a domain account?

Abecedarian

Note: You can't pass Windows user name and password in the connection string to log in to SQL Server as detailed here. It has to be a SQL user login. – Litch

@Litch This is not strictly true anymore, Active Directory Password is a supported auth scheme now. – Ricketts

Answer 1

Have a look at connectionstrings.com for every possible variation - a very handy resource I use all the time

Specifically, you want this format:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

This, of course, only works if the domain account in question is the one opening the connection.

There's no easy way to connect with arbitrary credentials - but you can impersonate the user in question and then connect.

This can be a bit of a pain. An alternative if the users are on the local network (or you control their browser config) is to use Kerberos authentication on your site. The pages will be served with the relevant user's permissions - then you can use the connection string above and IIS will connect to the Db with the appropriate credentials for each user. This is particularly useful from a security perspective as the Db is able to audit on a per-user basis, and permissions can be per-user/row/column instead of only per-app.

Ease

Thanks for you response. Now another questions come up. So to use a domain account in the Connection String I would have to be logged into windows/PC using the domain account is that the only way it could work? – Abecedarian

It's not who YOU'RE logged in as, it's who the website is running as. Eg Go into the application pool and change the user in there to be (say) MyDomain\MyWeb.Service - then grant permissions for the database to that user. This is the simplest way to do it - but all connections will be done to the DB as that one user. If you want the connections to be specific to the permissions of each user (eg for PCI compliance) you need to look into passing through the credentials - either by pickingthe proper auth model or using impersonation – Ease

Welcome. Incidentally, you can further harden this by making sure the domain controller flags the account as a "Service" account and shouldn't be used for interactive logon - That will prevent some numpty trying to log into windows as the web server, and then access the DB. – Ease

www.connectionstrings.com is cool this worked for me: Initial Catalog=SpiraTest;Server=SERVER.COMPANY.COM;Database=DATABASE;User Id=LOGIN;Password=PASS; – Aboard

@Aboard The point here is that will only work if the account is a SQL account, not a domain account. – Rumney

Answer 2

If you want to use different user account then the logged in user you have two options.

Option 1

You can add the user to Application pool Identity.

For this go to advance setting of application pool and edit the identity to use the user you want.

Option 2

Add this in Web config:

<identity impersonate="true" userName="Domain\User" password="Password" />

And use this connection stirng:

<add name="Name" connectionString="Data source=SqlServer;Initial Catalog=DbName;Integrated security=True" providerName="System.Data.SqlClient"/>

For More Details See: https://msdn.microsoft.com/en-us/library/134ec8tc.aspx

Also found another good article here https://www.codeproject.com/tips/520341/implement-impersonation-in-asp-net

Jehovist

Answer 3

Use integrated security:

Integrated Security=SSPI

Which has a variant:

Trusted_Connection=True

The different connection strings (for a variety of databases) can be found on connectionstrings.com.

With both of these you need to ensure that the application is running under the account you need to login with.

Overwrite

Answer 4

Yes, try this:

Data Source=server;Initial Catalog=database;Integrated Security=SSPI;

This specifies that you wish to use integrated Windows authentication where you were still trying to use SQL Server authentication (even though the username you entered looked like a Windows domain / user account SQL server still treats it as standard SQL Server authentication)

Also take a look at connectionstrings.com

Parshall

Answer 5

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

The above is a connection string for Windows Authentication against your SQL Server instance.

Levitt

Answer 6

Ran into this issue just recently trying to auth to an Azure Sql Server with an AAD user using Invoke-Sqlcmd. Adding the auth scheme to the connection string resolved it. I suspect this bug only affects SqlServer module v21 and below as I was not getting it using v22 on my local machine.

"<ConnString>;Authentication=Active Directory Password"

Ricketts