>Connecting to SQL Server using Windows Integrated Security across domain boundaries

>In the project I’m currently working on we had a requirement to use Windows Integrated Security to connect to a SQL Server 2000 database. The “gotcha” was that the database is on a Windows 2003 Server and a member of a domain. The web server that needs database connectivity is not a member of any domain.

It turns out that it is possible to use integrated security in this scenario if you configure things correctly. You’ll need to create local machine accounts on both machines and those two accounts must have identical usernames and passwords.

So, for example we have a SQL Server running on ‘mysqlbox’ in the ‘DEMO’ domain and a web server named ‘mywebbox’ which is not a member of any domain.

1) Create a local user account on ‘mysqlbox’ named ‘testuser’ with a password of ‘password’.
2) Create a local user account on ‘mywebbox’ named ‘testuser’ with a password of ‘password’.
3) Configure your application on ‘mywebbox’ to run under the identity of the account created in step 2 (so mywebbox\testuser). In our case we had a web application so the App Pool that the web app runs under was configured to be ‘mywebbox\testuser’.
4) Grant access privileges in SQL Server to mysqlbox\testuser (note that you do not grant access in SQL Server to mywebbox\testuser at any point… that user is not know to the mysqlbox server).
5) Set the connection string for your application to:
Server=mysqlbox;Database=MyDB;Integrated Security=SSPI

At this point your should be able to run your application and connect to the database using integrated security. In the database you will be connected as mysqlbox\testuser even though your application is running on mywebbox as mywebbox\testuser.

Good times.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s