July 13, 2005 at 8:00 am
Please tell me there is a way to use integrated security across to boxes!
I have an intranet server that runs both IIS and SQL Server. My ASP applications make use of Integrated windows authentication, so that I can set permissions not only on the ASP pages themselves, but also on objects within SQL Server.
I recently obtained another server on which I have installed SQL Server. I want to move all the databases on my original box on to the new one and have two servers - one to server the pages, and one to store the data.
I thought it would be a simple case of changing the connection string from something like:
sCn="Provider=SQLOLEDB; Data Source=ORIGINAL; Initial Catalog=dbDatabase; Integrated Security=SSPI"
to
sCn="Provider=SQLOLEDB; Data Source=NEW; Initial Catalog=dbDatabase; Integrated Security=SSPI"
However, after getting the error message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
..I started looking around on the web and found out that it is impossible to use windows authentication between an IIS box and a SQL Server box, even if they are both on the same domain!!
Can someone please tell me I'm stupid, and show me how to do it!?
July 13, 2005 at 8:34 am
It's not impossible. The catch is what context the web application runs under. You probably have the web app running where anonymous connections are allowed (your users aren't being prompted for credentials or if they are, it's solely by the application not the web server). In that case you need to change the account used for anonymous connections to a domain account that has the appropriate privileges in SQL Server. By default it's IUSR_ and it's an account local to the IIS server.
To change it, right-click on the application in the Internet Services Manger and choose properties. Click on the Directory Security tab and click on the Edit button under "Anonymous access and authentication control." Then click on the Edit button beside "Account used for anonymous access." Set this account to be the domain account. In this case you'll want to manually set the password, so do NOT let IIS control the password.
K. Brian Kelley
@kbriankelley
July 13, 2005 at 8:48 am
No - there is no anonymous access whatsoever. It is disabled. Only Integrated Windows authentication is used.
It seems as if the security details of the person viewing the page is not being pushed through to the SQL box ("Login failed for user '(null)'")
July 13, 2005 at 9:16 am
In that case you're running into a double-hop issue. NTLM (NT 4 domains) doesn't allow it and you can get around it in Kerberos (Active Directory) by configuring delegation. Basically what's happening is you have a Windows authenticated connection to the web server. That's one hop. If you're trying to do Windows authentication from the web server to the SQL Server using Windows authentication, that's a second hop. And that's where the problem lies.
There are several ways around this, but they al have their ramifications:
1) Use basic authentication to connect to the web server. This will prompt for username/password. It's an annoyance to users and you'll probably want to SSL this. However, the first connection isn't a hop so you can go to the SQL Server from the web server.
2) If you're running under AD, you can turn on delegation for the users and set the server to delegate. These have some significant ramificants and you'll want to review the applicable MS documentation. It's also increased work on your security administrators.
3) Use a single SQL Server login from the web server back to the SQL Server.
K. Brian Kelley
@kbriankelley
July 13, 2005 at 10:43 am
Wow. I'm quite surprised about that. I put our extranet together with a different authentication method (obvioulsy, can't use windows authentication across the web!) using a web server and separate SQL Server box, but never thought that it couldn't be used when both the web server and sql box were on the same domain.
Do you think this is something microsoft could have developed - it surely affect thousands of developers!!
Do you know what the most common approach to authentication on an Intranet application with a separate IIS box SQL Server box?
July 13, 2005 at 11:03 am
It's not a Microsoft design issue, per se. NTLM's limitation is due to security reasons and I think it a holdover from IBM's LAN Manager protocols. Kerberos is an Internet standard developed initially at MIT.
As for how to handle authentication, I'm used to seeing it as Basic Authentication. For instance, BindView's bv-Admin tool, when you use the web interface, requires basic authentication. Granted, it's impersonating the user to carry out functions within Active Directory, but impersonation is impersonation. Otherwise, if all users have the same rights, I'm used to seeing a SQL Server login to connect to the SQL Server. If you have to restrict WHO needs access (but all valid users have the same access), this works well and you restrict the WHO using NTFS permissions on the web server.
K. Brian Kelley
@kbriankelley
September 2, 2005 at 9:28 am
We are working with this issue right now and have 2 important questions about using the Basic Authentication for connection to the SQL Server. We do not have AD and have the same issue described here, so BA seems to be the answer for now.
First, during impersonation with BA will the user name and password be encrypted during transmission and connection to SQL Server from the web server? We know it is sent plain text from client to web server, but how about IIS web server to SQL Server?
Second, since many aspects of a user connection are stored server side in cache on the web server, if the system was breached could user names and passwords be retrieved from this cache? Since the data is sent plain text from client to web server, is this data stored plain text like in cookies or stored in an encrypted format?
Any help is very much appreciated.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply