June 4, 2002 at 6:43 am
Can this be done with IIS 4.0 and SQL 2000?
We are passing the connection string below and getting errors.
SERVER=WEBDEV;DB=reports;PROVIDER=SQLOLEDB;UID=b279171 (this is the NT account)
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'B279171'.
/execrep/index.asp, line 14
TIA,
Matt
June 4, 2002 at 7:42 am
The method you are trying to use will not persist and use a trusted connection. When you use a trusted connection you will not need to pass the username or password as it will use the NT sid for security.
To get this to work in IIS first open the secuirty of you site and remove Anonymous from login. If the users are connected to the same domain as the IIS and SQL servers nothing needs to be done. If not then you will need to turn on Basic Authentication and set the domain to authenticate to (note this sends username and password clear text unless you get a encryption certificate and require SSL to connect, also this will put up the ugly authentication box you see on some sites).
Then in you provider string for SQL you will do like so.
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBName;Data Source=SERVER
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/04/2002 07:43:53 AM
June 4, 2002 at 12:21 pm
so... if the user's, IIS and SQL are on the same domain we do not need to pass a user?? the NT sid will be used automatically?
thanks again.
June 4, 2002 at 3:05 pm
Right, unless I forgot some key element but I never get asked to login on the 1 site I have it setup thast way on.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 9:09 am
This isn't possible with IIS 4.0 using NT authentication all the way through because of the double-hop issue (NTLM restriction by design). You'll have to toggle Basic Authentication (users will prompted for username and password) for the web site. See the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q247931
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 5, 2002 at 9:41 am
Brian - that was a great help... I think I have this nailed.
June 5, 2002 at 10:15 am
I wouldn't suggest doing this for a publicly accessible website because it's probably going to open up a security hole. Any user accessing your site will have access to your database.
Myself I wouldn't even run this configuration on a private network.
June 5, 2002 at 11:34 am
Thanks Brian, sorry I use IIS5. As for security even on a private network I would suggest using SSL to connect (128Bit) and if you can get Verisign Certificate or other Micrsoft provider (already setup in IE). Or if you set up your own Certificate Server the users will most likely have to click yes on a screen that is complaining about the certificate.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 11:55 am
FYI - we will be using SSL and this is an internal app... the ASP will not be exposed to the web.
June 5, 2002 at 1:19 pm
Keep in mind, though, that the majority of computer intrusion attacks are internal, at least with respect to a Department of the Treasury study (USA) on financial institutions. Ensure you do the standard things for IIS... remove the samples, don't install to C:\, remove the ISAPI mappings you don't need (.htw, .idq, .idq, etc). Microsoft has security checklists for both IIS4 and IIS5.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 5, 2002 at 1:23 pm
will do... thanks again for the help.
June 10, 2002 at 9:45 am
So this is possible with IIS 5.0? I was trying to use Windows security on an ASP.Net page and connect to a SQL Server using Windows authentication. I was getting the error that "login failed for user <null>" when I try to connect to the page from a different computer. It worked fine when I opened the page on the same machine IIS was running on. I was using impersonation.
Thanks
June 10, 2002 at 10:26 am
Maybe not, I just double checked, and cannot get to do right off. I thought we had gotten one this way previously in testing but so many changes I cannot find it. If I do I will let you know for sure, or if I get a chance to play with a bit.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 10, 2002 at 10:46 am
I would never suggest doing this but you might need to change the IUSR account to a guest domain account and then grant that domain account access to your database to get this to work. I'm sure my security guy would smack me in the head for even thinking about doing this though. 🙂
Not sure if this would work but it seems to make sense that it would.
June 10, 2002 at 10:56 am
When you use NT Auth, you do not specify a user for pwd. The user account of the logged in user, in this case the IIS Service user, is used. This is not an IIS thing, but a windows thing.
In IIS, on the security tab of the website, you can specify a user account to use for anonymous IIS users. You can give this account rights to the SQL Server and connect. However, if your users are authenticating to IIS with their windows accounts, then their windows accoutn will be used to connect to SQL (using Windows Auth). If you do not want to setup all users in SQL, then you will need to use SQL Auth and set a user and pwd in the ASP app.
Steve Jones
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply