November 19, 2002 at 2:26 pm
I'm wondering how many of you are building web applications, and using exclusively Windows Authentication (no sql server authenticated logins)?
We are running SQL Server 2000, and IIS 5 in a Windows 2000 environment, without using AD.
I've tested setting up IIS to use a domain account for an anonymous access, as well as "Basic Authentication" and "Integrated Windows Authentication".
The anonymous access works fine, and I'm guessing is relatively secure, provided you only need one SQL Server security profile for your application, or your application handles security profiles.
The "Basic Authentication" I understand broadcasts the user and password as clear text, so probably not a good choice for an Internet, and maybe even an intranet application, although allows a web application to logon to SQL Server using different domain accounts.
"Integrated Windows Security" works, although will not provide double-hop support (our IIS box and SQL Server box are not on the same machine).
How are most of you handling Integrated Security connections to SQL Server via web application? Anonymous, Basic, or Integrated Window security with IIS and SQL server running on the same machine, other configurations.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 2:48 pm
Internet - Use an anon user with Integrated security. Trap the user/pwds in the app and store them (encrypted pwd) in a table.
Intranet - Using Windows auth. Works well.
Steve Jones
November 19, 2002 at 3:03 pm
Are you saying you have set the "Basic Authentication (password is sent in clear text) option in IIS for you intranet applications? If so would this not allow someone in your organization (like a network guru) to sniff for user password?
Would you expand the non-user option? I'm not sure I really follow. Is an anonymous user logging on to SQL Server, and the application is controlling the access via the non-user information provided? If so are you using application roles to control access, or only which web pages get displayed?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 7:20 pm
quote:
The "Basic Authentication" I understand broadcasts the user and password as clear text, so probably not a good choice for an Internet, and maybe even an intranet application, although allows a web application to logon to SQL Server using different domain accounts.
This is untrue, I use it all the time on our intranet. The thing is I set up my own Certificate server and placed a 128bit certificate on the site and only all HTTPS SSL connections. Setting up a certificate server is not hard, the problem is it is not a standard recognized IE certificate provider and they get a message about that. If you want to not have the little popup about this then you can get a Certificate from Veritas for I believe around $250 a year (note this price may be if they setup your own corporate server to issue under them, I think we have one).
Thus basic authentication is fine because the data stream is encrypted.
November 19, 2002 at 7:48 pm
We are building strictly for Intranet, not Internet, and use Windows Authentication exclusively. We don't allow anonymous access, this gives us some record of who is accessing what. By limiting users (who are employees with company PC's) to IE as a browser we get simpler web pages and less testing needed. Not that IE is necessarily better -- but one browser is easier to target.
We also are using AD and delegation and that works quite well as well. That way we can run queries under the user's ID when appropriate, and we set the access controls in the database and not (just) in the web pages.
Works well. Cheap. Easy.
November 20, 2002 at 7:38 am
We are not using active directory right now, and I think it will be sometime before we get there.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 7:40 am
So is it my understanding that if you set up a certificate server with SSL then the passwords are not broadcast as clear text, but without certificate server and SSL passwords are broadcast as clear text? Do know of a place I can go to read up on setting up a certificate server? Isn't there some overhead also in performing SSL?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 6:02 pm
Sorry, did not exactly state right, you just have to have a Certificate instaled on the IIS server for your site. I used Certificate Server to generate my own internally, or in some cases call Veritas and order one. You don't have to have the server on site or even up and running, just for the issuing of them. As for running one I have no documentation and it took me a bit to figure out. I also have not seen any site on this. If you need further help I may not be of use anytime soon, but I can do a quick walk thru on it when I find time.
November 20, 2002 at 6:27 pm
Do you mean Verisign instead of Veritas? Veritas makes backup software. Verisign manages domains and is a trusted root certificate authority.
As for Basic authentication... If you install a certificate and force SSL, then yes, the entire conversation is encrypted, to include the password transfer. This is the same technique used for secure ordering over the internet.
IIS won't then turn around and transmit the username and password in cleartext, no. It'll be able to impersonate the user and pass NT credentials along. This is true of both NTLM and Kerberos.
If you are on AD and it's intranet, you could use delegation to ensure secure authentication credentials all the way across.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 20, 2002 at 7:26 pm
Yes, sorry, Verisign, having a brain F@$t day.
November 21, 2002 at 7:42 am
Brian, it is my impression that you can't use Integrated windows security to perform double-hop authentication. Meaning if a client is logoned on as a domain user, and the callup a page on an IIS box, then the IIS box passes a SQL command to a seperate SQL Box, the SQL box will not be able to authenticate the user. But if IIS and SQL live on the same box (single hop) then SQL can authentic. Also my testing seemed to confirm this. I'm guessing from your comment that this might not be true. If so what does it require to get this to work if you are use NTLM and not Kerberos.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 21, 2002 at 8:16 am
You are correct, NTLM does not support double-hop, period. That is by design in the security mechanisms.
Kereberos supports delegation, but you have to do the configurations on users/servers/etc.
If you are in an NT 4.0 domain, or an AD Mixed Mode domain where NT 4.0 is playing a part, you can't double-hop. The easiest way to see this is to create a linked server connection which uses the user's credentials, then connect to the first server with Windows authentication and try and access that linked server source! The work around is one of those two paths has to be a SQL Server login.
We see the same problem with applying security for FrontPage. You connect to a FrontPage web and depending on how the web server is configured, you either connect via Basic Authentication or Windows Authentication. A common security device is to set the registry setting on the domain controllers so that anonymous users don't have the ability to do lookups. Why does this matter for FrontPage? Because of the double-hop.
If I connect to the web server through FrontPage using Windows authentication and we're NTLM all the way (or Kerberos without delegation), the best the web server can do when trying to retrieve the list of groups and users for a particular domain is to try and do so anonymously. If we've made that registry change to prevent such access, you get the idea.
There are therefore two choices. One is to enable anonymous. Okay, forget about that. I put that in to keep everything secure! The second is to turn on basic authentication and force users through that. Yes, that means a prompting for a username and password in most cases, but IIS can then take the username and password and impersonate the user on the server. When it makes the connection to a DC, it's only a single-hop. Therefore NTLM is satisfied and you can enumerate the users, which means you can manage security through FrontPage. Of course, there's that nasty problem of a domain username and password going across in clear text, hence the use a server certificate and SSL.
The same is true of IIS and SQL Server. Basic authentication means the user can be impersonated on the IIS server and that's th start of the "Windows" authentication. As a result, the connection to SQL Server is a single-hop.
Relevant KB articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;176377
http://support.microsoft.com/default.aspx?scid=kb;en-us;247931
http://support.microsoft.com/default.aspx?scid=kb;en-us;253500
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 21, 2002 at 9:03 am
Thank you for the links and the information Brian. My goal here is to use Windows Authentication. Most of our application use a single SQL Server login for all users so I guessing the Anonymous window login will work just fine. Although we do also have intranet applications where each user has a seperate SQL Server logon. I guess if everyone is find with Basic authentication and clear text password we are good to go. Although I think because of the nature of our data this may not fly and we will have to use the Certificate/SSL approach.
Now here comes the question. How much overhead (performance) is associated with turning on SSL and using Certificate? Also do you have any good links to help configure IIS for using certificates and SSL?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 21, 2002 at 10:01 am
What version of IIS?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 21, 2002 at 10:25 am
We are running IIS 5.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply