Thursday, May 31, 2007

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.

No comments: