May 7, 2002 at 12:48 pm
Hi,
We are having trouble connecting to our database server when an ASP page on our Web Server(each resides on its own machine) issues ADO's Connection.Open method. The SQL Svr DB engine is set for Windows Authentication and Web Server is using Basic Authentication as described in Microsoft's Q176379 (IIS and SQL Svr on Sep. machines with trusted connection). We receive the error: Login failed for user 'NT AUTHORITY/ANONYMOUS LOGON'. Any insight in terms of SQL Svr db settings, permissions or windows account concerns would be appreciated!
May 7, 2002 at 9:33 pm
To tell you the truth Ive never know anyone who was using Windows Authentication on SQL server in conjunction with a web application that used basic authentication. Generally what is done is mixed mode authentication where a sql server user is created specifically for the web application. Then the web applicaiton uses the sql server login and password in the connection string. If possible I would recommend using sql server logins or if your web users have nt accounts then change the web site security to Windows NT.
Otherwise I guess you could could try setting up a shared folder on the sql server and allow access to the guest group. Then try logging in from another machine with either the guest user (need to enable if disabled) or the IUSR_MACHINE account (never tried logging in locally with this account before). Once logged in just make sure you can access the share. This way you will see if windows authentication is the problem or if it is something specifically between sql server and iis server.
May 8, 2002 at 5:14 am
Sounds like your connection string is not using the statements to utilize trusted connections. I have done this only a few times, mostly to see how it works. Otherwise I do as Leon suggests but am moving to an LDAP corporate solution later. This is the way your connection string needs to look.
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDBNAME;Data Source=MYSERVERNAME
Also make sure you did remove the Anonymous login option from the site as it will take precedence in most cases.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 11, 2002 at 8:40 am
I got:
Number: -2147217843
Description: Login failed for user 'IUSR_<ComputerName>'.(54)
Class: Connection
Procedure: ConnectDB
The IIS and SQL 2000 are on the same machine. SQL uses mixed mode authentication.
How do I resolve the problem?
May 11, 2002 at 10:34 am
kduan even thou SQL and ISS on the same box is considered bad to do it does not matter where they exist in relation to each other. If it gave you IUSR_<ComputerName> this is the IIS anonymous account when you have anonymous login turned on. Open IIS and right click the Site this is for, there is a Security dialog for logins on one of the tabs (sorry forget the title since I don't have a test server here at home). But it will have 3 items, Anonymous, Basic, NT logins of which by default Anonymous and NT will be checked. You will need to tunr off Anonymous and anyone that does not connect directly to the same domain or server you will need to turn on Basic which will bark about not secure and password not protected when sent (I'll explain in a minute). When you do this there is a button to set where the user accounts will come from. Type in the domain name otherwise they will have to when prompted and press OK. Once this is done OK out to IIS manager and test. A user should get prompted for their username and password, when they enter they will be submitted via NT authentication and if they enter a valid username and password that has access to the SQL server via the account or a global group with access then all should go fine, otherwise after three tries they should get the same error you got but with the username they entered. Now as for the password protection if this is in house you can set up a certificate server and generate a 128 bit encryption certificate to handle this (but that is really painfull and has a lot of special needs to get to work seemleassly), or if inhouse leave as is and not worry about it (not the best idea but if a protected network only hackers are those inside, or lastly and the best suggestion is to get a Certificate from an MS recognized vendor like Veritas which runs about $200 or so per year (at least for us but may be some special discount for how many we get). If you have troubles, please let me know what ytou did step by step and I can help you make corrections to your steps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 3:36 pm
Thanks so much for your prompt response! The IIS virtual directory security settings that we have are: Anonymous access (enabled). Integrated Windows authentication (Checked). Basic authentication (Unchecked). It needed to be this way because everybody should have read and write permission to the database. The strange thing is that I can resolve the problem by restore the databases from the backup. The backup was done from a working server. So that make me think the error is not from the IIS setting. I compared the users and roles from these two databases and wouldn’t find any difference. Both of them grant read and write to the public role. The other thing that I can think of is that everything was set up by using domain administrator account in the working server. The job to run the database creation and permission setting was run by using the domain administrator account. While as, a domain user account was used in the problem server although it is belong to the local admin group. I am not sure whether this will make any differences.
By the way our network is protected by firewall. Only http and https traffic and come through.
Looking forward for your response. Thanks again!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply