SQL Server 2005 login authentication across different domains

Posted on 2009-12-29
Last Modified: 2013-12-17
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?
Question by:eaquiroz
    LVL 13

    Expert Comment

    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.

    Author Comment

    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?
    LVL 13

    Accepted Solution

    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.


    Author Closing Comment

    This guy really knows his tech!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
    If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now