Connecting to SQL Server from .NET using an Active Directory Service Account

Question

I have Winforms application written in C# which connects to a SQL Server Database. When I connect to SQL Server I build up a connection string which can contain either SQL Server login details or use Windows Authentication, in which case I omit the user name and password and use the

"Integrated Security=SSPI" 

setting in the connection string.

Now, a user requested that they have the option to connect to MS SQL Server using an Active Directory Service Account as opposed to a Network User account (which is what I assume the connection using Windows Authentication will pass through.

I am not familiar with service accounts or Active Directory and was wondering if someone could point me in the right direction. Is there some way of building a connection string that will allow my application to connect to the database using a specific Active Directory Service Account?

Answer 1

Connection strings have nothing to do with this.

a user requested that they have the option to connect to MS SQL Server using an Active Directory Service Account as opposed to a Network User account

What that means is that the user has requested your application to run as a service account, not as the currently logged in user. One easy way to do it is to simply start the application under runas /netonly:

runas /netonly /user:domain\serviceaccount MyWinFormsApp.exe

This way your application runs as a domain service account on the network and it will connect to the SQL Server using the domain\serviceaccount credentials. This would satisfy your client's requirement, at least on a shallow surface cursory look.

If the solution of using runas is not satisfactory (client may legitimatly complain that it requires users that start the applciation to know the domain\serviceaccount password) then things get a bit more complicated. The correct way of doing it is o split your application in two, an UI presentation layer .exe application that runs under the logged in user credentials and a business logic layer component that runs, as a service, under the domain\serviceaccount credentials. The two components communicate using your IPC of choice (usually WCF). As you probably realize, this require s major rewrite of your application.

Some may suggest to use have your application impersonate the domain\serviceaccount before opening the connections to the database. I would strongly discourage that because of the serviceaccount password storage/retrieveal mess. Since the app will be required to know the serviceaccount password in order to impersonate it, the user logged on running the application will either know that password or easily be able to find it (there is no way to prevent him form find it if the application can find it). Since the domainservice password is accessible to the logged in user anyway, he may just use the runas /netonly solution. And that finally explain why the runas solution is just a shallow smoke and mirrors solution: the only reason your client may had requested what he requested is that he wants to separate the privileges of the users logged in from the privileges of the application (ie. don't give SQL Servera ccess to every employee). Since the runas solution (as well as impersonating in the app) require the logged in user to know the service account password, the separation of privileges does not really occur since hte logged in user can use any time it wishes the service account password and elevate his privileges to access the SQL Server database at will. Therefore the only solution worth talking about is the separation of the application in two parts.

Answer 2

Connection string "Integrated Security=SSPI" will pass the current user credentials to SQL Server. However if the user want to use other Active Directory User without changing the connection string, use Run As functionality offered by the Operating System. If you are using windows 7, press Shift and Right click on the exe and choose Run as different user.

This is just a hack not a proper solution.

Answer 3

Generally speaking, an active directory account is the same as a network user account. There are exceptions, such as Workgroups and non-AD networks, but for the most situations, they will be the same.

If your users are on a non-AD network, then your users are asking for something quite complicated (cross-domain trust between different network types).

However, if your users are running in an AD network, but want to log on as a different user than their interactive user (for example, they are logged on to their machine as MJames, but want to log into the database as FJones), then you have two choices:

1) Tell them that they need to log on to their machine as the requested user.

2) Prompt the user for their login credentials and that execute an AD login to validate their credentials, then impersonate that logged on user using the .Net framework. This MSDN link, although for ASP.Net, has the basic information that you need, specifically the section on Impersonating by Using LogonUser.

In any of the above cases, your Integrated Security=SSPI command string argument will connect to the SQL Server as the AD user that your user is currently logged on as, either directly to the machine or through impersonation.

Answer 4

This is the connection string I tried and it worked for me. Yes - Integrated Security=SSPI is needed.

"Data Source=myServerName\myInstanceName;Initial Catalog=myInitialDB;App=myApplication;Integrated Security=SSPI;"

Answer 5

you can connect with a specific user to a database by setting the Identity of your application pool to that specific user. Of course, that same user should have all necessary access to execute that pool.

Answer 6

"Integrated Security=SSPI" will pass along the credentials of the currently logged in user. A service account is just another user, albeit one you will actually have to specify in the connection string. I assume that by default, you will want to just pass along the logged in user by using "Integrated Security=SSPI", but offer the option to specify credentials to login with, in which case you would use a connection string that specifies the username and password.