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;Inte
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?
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;Inte
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?
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This guy really knows his tech!
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.