April 3, 2006 at 3:20 pm
I have always used SQL server authentication.
Database is 2005 running on Windows 2003
Web Server is running Windows 2003
In the SQL Server 2005 Management Studio, I have tried to add a login for the web server but have had no success. I’ve noticed in the search option for adding logins the only object types for logins that are available are "users", "groups" and “Built-in security principals”. It does not specify servers or computers.
It seems like this should be easy. I feel like I am going to the wrong “Add Login” page or something.
Thanks for the help.
Davant
April 3, 2006 at 10:12 pm
> I’ve noticed in the search option for adding logins the only object types for logins that are available are "users", "groups" and “Built-in security principals”. It does not specify servers or computers.
Servers do not log in to databases. Users do. When the web server connects to the database server, it's actually the user account under which the web application is running that logs in, or by impersonating the user accessing the web server in an integrated scenario. Depending on what's doing the connecting, the specific user account used in the connection attempt may change - it could be the IIS user, the ASP.Net user, the user configured for the COM+ package, a specific account forcibly impersonated inside the application...
The SQL Server must be able to authenticate the user account. If the web server and SQL Server are running as stand-alone (non-Active Directory) mode, then they do not trust each other, and each server cannot authenticate the local user accounts defined on the other.
Using Windows authentication, the web application must access the SQL Server using an Active Directory account that has been granted the SQL Server access you require, or the web application must impersonate the credentials of a local Windows user account on the SQL Server that has been granted that access. If going the Active Directory route, then the web server and SQL Server must reside in the same domain, or trusting branches of the same forest, in order for the SQL Server to authenticate the user account through Active Directory.
An easy way to see which account the web application is trying to use is to run Profiler and audit failed logins. Then try to connect with the web application and see what pops up.
-Eddie
Eddie Wuerch
MCM: SQL
April 4, 2006 at 9:20 am
IIS allows an account to be specified for anonymous users. Be sure this is a basic domain account with only the permissions needed to access only the data needed.
April 4, 2006 at 9:56 am
It is possible to use Windows "passthrough" authentication between a web server and a SQL server. To set it up you need to have identical local accounts on the web and SQL server. For example, you can have a local account named IUSR_WEBSRV01 on the web server and another account named IUSR_WEBSRV01 on the SQL server (they must have matching passwords). Then create a SQL login for that user and map it to the local IUSR_WEBSRV01 Windows account and grant any appropriate access to your DB. This will allow two machines that are not on a domain to communicate. MS does not support this but it does work.
April 5, 2006 at 3:36 pm
Is AD required for this to work?
April 6, 2006 at 6:07 am
No, AD is not required. Our IIS servers are on an AD domain but our SQL servers are in a workgroup. Only local accounts are used so AD never comes into the picture. It's kind of a kludgy setup but we were forced by management to take our SQL servers off the domain, otherwise we would gladly use a domain account instead.
However, we are running Windows 2000 for IIS and SQL. We will soon be upgrading both to Windows 2003 (within a few months) so I will let you know whether it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply