I am running Winn2k with SQL Server 2000 and IIS 5

I go into IIS and right click on the website I am working on and choose properities, then select the direcroty security tab, click on edit anonymous access and authentication control, and then click on edit anonymous access and enter IUSR_MACHINE without allow IIS control password.

In SQL server in make sure that the users account that I want to use is added to the sql login with the correct permission. I then swicthed from
Widown Only authenticatin to sql server and windows authentication.

In the dsn I switched from With Windows NT authentication using the network login ID to With sql server authentication using a login ID and password entered by the user. Under Connect to sql server to obtain default settings for the additional configuration options I put in the user that I have added to sql server.

When I try and run the web page I get the following error message and I am not sure why:

Not associated with a trusted SQL Server connection.

Any help would be greatly apperciated.

Sincerely,
Kim
Database Programmer

Did you modify the iusr password ? This can create major syncing headaches. Is the iusr added to sql server ? Please post your connection string.

Also take a look at the following article:

article

I have added IUSR_Machine to sql server.

No I have not changed the password. I don't know what the password is for IUSR_Machine.

I read the article and I have done all the things that it suggested and it still doesn't work.

Thanks

Is "Allow IIS to control password" checked or unchecked ? If it is unchecked - what prompted you to uncheck it ? Did you have it working before these modifications ? Please post the connection string. Is the error the same and have you looked at the event viewer for additional information ? Is IIS and SQL server on the same machine ?

There is a checked mark in IIS. I would never remove the check mark when I use IUSR_Machine.

Yes both IIS and SQL and the website are all on the same server.

The only way I had the site working before was to use and administrator user name and password in IIS. This unfortunately caused other issues and I can no longer use and admin account to access sql.

Here is what I am using as my connection string.

set objRs = server.createobject("ADODB.Recordset")
strSQL = "SELECT * FROM TABLENAME"
objRs.open strSQL, "dsn=somename"

This is how I connect to all my databases.

Hopefully this is what you mean by my connection string

First, try the following:

modify your objRs.open

objRs.open strSQL, "dsn=somename;UID=UserinSQLServer;PWD=Passwordofus er"


Where did you use the admin user name and password within iis ? Have you tried to go back to the working scenario to verify that it still works ? Have you tried a dsn-less connection ?

I have added the username and password into the connection string and that seems to be working.

I did try and put the admin username and password back into IIS in directory security, anonymous access and authentication control, anonymous access and it still worked.

I have since removed the admin username and password put the check mark back in Allow IIS to control password and put IUSR_MACHINE as the username.

My boss doesn't want me to use a dsn-less connection.

Thanks for all your help.

I am however having a problem on my other server. I use the same connection string as you have suggested. On the server with sql server I am using IIS 5 but on my other server I am using IIS 4.

The error message that I get now is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Any suggestions would be greatly apperciated.
Kim

Give me a detailed description of this 2nd scenario - Is the iis 4 version on a different machine than the sql server (is this the same sql server you were originally talking about) ? Give me os/iis/sql server versions involved. And authentification (user ids) involved.

IIS 4 is on a different server that has winn2k on it. SQL Server 2000 is on another of my servers.

My one server name is Mars and my other Server name is Pluto. Mars is a winn2k machine with sql server 2000 and iis 5 and pluto is the second server with winn2k and iis 4.

On Pluto I went into iis into directory security and made sure the check mark was on in allow iis dto control password. The username I am using is IUSR_PLUTO.

my connection string looks like this:

"dsn=jaycee;uid=userid;pwd=password"

thanks

Does iusr_pluto account have a login for the sql server - probably not. How you normally handle this is by creating a domain level account. This allows you to have logins on 2 different machines - this login would be used for the anonymous security and accessing sql server. Read the following article:

article

Let me know if you need any help.

I have added IUSR_Pluto to the sql server login group. How I did this was by opening Enterprise Manger, Microsoft SQL Server, SQL SErver Groups, Mars, Security, Logins, new login.

I have read the article and tried the following.
In my ODBC connection I have selected SQL authentication and put in the administartor username and passwrod that I will be using and have used for my other connections.

I know that using and admin password in IIS under directory security will work but as I mentioned before this leave us with security issues.

Could you tell me how to create a domain level account and what I should do with it.

Thansk for all you help.

Kim

Try the following - use the iis 4 as a test case (why do you have iis 4 installed on a w2k machine ?):

1. Create an IUSR_????? account on the domain
2. Add this domain account to sql server.
3. Grant logon locally to IUSR_???? for any machines it needs to access
4. Add the domain level iusr account to the iis 4 machine as the anonymous account
5. Stop/start iis.
6. Test - first checking that you can render a simple asp page, then try a page that hits the sql server.

Also, depending on how your file permissions are currently setup you may need to grant permissions to the iusr_??? account. I also seem to remember permissions issues with dll files as well.

Start with the iis 4 machine first - once you have everything functioning correctly - then change the iis 5 machine.

I also recommend that you research the internet as well to see if there is documentation that may help you here.

Let me know how it goes.