SQL Server 2005 login authentication across different domains

Hello Experts,
I am trying to find a database authentication solution across different domains (computers) using only SQL Server 2005 login credentials. I am not using a table with login accounts but need to use the login accounts in SQL Server. One computer is the database server "XYZ" with SQL Server 2005 and the other is a web server "ABC" with many similar .NET applications. On the web server "ABC" the .NET apps are written in both C#/VB.NET with "Integrated Security=SSPI" in the connection string like shown below:

Persist Security Info=False;Data Source=XYZ;Initial Catalog=DATABASE_NAME;Integrated Security=SSPI" providerName="System.Data.SqlClient

The web server "ABC" is using IIS 6.0 with "Enabled Anonymous Access = unchecked" and "Integrated Windows Authentication = checked". The database server "XYZ" has server authentication "SQL Server and Windows Authentication mode = checked" and has login accounts under "Security --> Logins --> (XYZ\username1, XYZ\username2, XYZ\username3, ...)" but when trying to access these accounts from the web server "ABC" the login accounts used are (ABC\username1, ABC\username2, ABC\username3, ...) The issue seems to be that the web server domain "ABC\username" is trying to access the database "XYZ\username" so is there a code solution (dll, web service, .net code, whatever...) where the ABC --> XYZ is switched or is there a setting that I am missing?
Who is Participating?
Unfortunately you are going to have a tough time getting this to work how you would like it.  

You must remember the reason why domains and workgroups exist.  Domains are inherently a trusted environment.  By definition workgroups are not trusted.  This is why in a workgroup you generally have to supply a username and password, and in a domain you can use integrated authentication.

You are trying to setup a trusted relationship where ABC trusts XYZ, but in a non-trusted environment.

However... you might be lucky and get this to work.  

Whats actually happening is the credentials that the application pool for your website is running under is trying to authenticate against the SQL datase.  This account is most likely the NETWORK SERVICE account.  Try this:

On ABC, create a local account and give it a password.  Set the application pool that your website runs under to use this local account (right click application pool in IIS console, select properties etc).  You will also need to add this account to the IIS_WPG local group, and under the local security policy, under user rights assignments, add it to:
- Access this computer from the network
- Adjust memory quotas for a process
- Allow log on locally
- Impersonate a client after authentication
- Log on as a batch job
- Log on as a service
- Replace a process level token

Do an IIS reset after, and open a local webpage to ensure IIS and the application pool are working correctly.

Now on XYZ, create the exact same local user account you created on ABC, with the same password as well.  Add this account to the SQL server and give it permissions to your database.

Now everything should hopefully work, although it may be a little flakey as this authentication is not really designed to be used across a workgroup.

The authentication flow will work like this:
IIS integrated authentication used to grant or deny the user permission to open the website --> The running IIS process, tries to connect with SQL, using integrated authentication, which are the credentials the app pool is running under --> SQL tries to match the username and password supplies by IIS to its local SAM database --> accepts connection if username and password match.

Are servers ABC and XYZ actually on an Active Directory domain?  Or are they in a workgroup?

From what you are describing it seems that the servers local user accounts are being used, rather than domain accounts.

If servers ABC and XYZ are in a workgroup, then you will need to use SQL login and not integrated authentication, because from a security point of view workgroups are not trusted.

If ABC and XYZ are on the same domain then integrated authentication will work.  However users accessing the website will need to be logged in with a domain account, and within SQL you will need to add the username accounts as DOMAIN\username1 etc, not XYZ\username one, as that would be a local computer account.

The problem you are seeing is that ABC is passing its users credentials over to the SQL server, however because this is a local account, your SQL server is not able to check the local security database on ABC to verify this is a legitimate user.
eaquirozAuthor Commented:
Both ABC and XYZ are not using Active Directory nor are they on the same Workgroup and are on different VLAN's but can access each other via files, ssl, and database access. Yes they are using only local accounts and not domain accounts. So is there a solution to use SQL Server 2005 login credentials with separate boxes just using local computer accounts? Is there a code solution (dll, web service, .net code, whatever...) where the ABC --> XYZ is switched or is there a setting that I am missing?
eaquirozAuthor Commented:
This guy really knows his tech!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.