Connecting to SQL database from Web server using ASP

Hi,
I'm creating ASP that should run stored procedure on SQL server. I need to set up System DSN, right? When I logged on to the web server as some NT user (which is administrator for web server and has read permissions on the SQL server) - I successfully created the DSN. When I logged on to the server as Internet anonymous account (we use IIS 4.0, so it is IUSR_SERVER-NAME), I was not be able to set up the DSN, though I did the same as previously. This means that we don't have appropriate permissions for web server Internet anonymous, right? So question number 1: can it be set up read permissions on SQL server for local user from web server with the name IUSR_SERVER-NAME?

I now that a user can be set on SQL and I can use it's id while setting up the DSN (while doing this I need to choose "SQL Server Authentication" and provide user id and password, correct?). I tried this way as well -- when then I use the set DSN in ASP file I still have the "Not defined as a valid user of a trusted SQL Server connection" error in my browser. So question number 2: How to set up system DSN and provide in it SQL server's user id and password?

Or may be the problem is that I use stored procedure and need different type of permission?

I would appreciate if you aswer all the questions or give me explanation on the problem.

Thanks,
Erik T.

Trusted connections in the web space require Basic Authentication for the site. This will allow the uuencoded password to correctly be translated by the web server and pass it to the SQL server. This means no anonymous authentication, however, and will only work in Intranet environments.

NTLM will not work in the web space for passing through the user and password. Therefore, trusted connections fail.

If you are using SQL server, I would recommend using a connection string instead of a DSN. This will make the script more portable. An SQL server connection string would allow you to specify the Server, the database name, and an SQL security ID and password. Then the web page could open the connection. The connection string would go in the same place as the reference to the DSN in the conn.Open statement.

Hope this helps. I spent quite a bit of time working out the connection information myself.


------------
Erik T at 7/14/99 4:38:02 AM

Hi,
I'm creating ASP that should run stored procedure on SQL server. I need to set up System DSN, right? When I logged on to the web server as some NT user (which is administrator for web server and has read permissions on the SQL server) - I successfully created the DSN. When I logged on to the server as Internet anonymous account (we use IIS 4.0, so it is IUSR_SERVER-NAME), I was not be able to set up the DSN, though I did the same as previously. This means that we don't have appropriate permissions for web server Internet anonymous, right? So question number 1: can it be set up read permissions on SQL server for local user from web server with the name IUSR_SERVER-NAME?

I now that a user can be set on SQL and I can use it's id while setting up the DSN (while doing this I need to choose "SQL Server Authentication" and provide user id and password, correct?). I tried this way as well -- when then I use the set DSN in ASP file I still have the "Not defined as a valid user of a trusted SQL Server connection" error in my browser. So question number 2: How to set up system DSN and provide in it SQL server's user id and password?

Or may be the problem is that I use stored procedure and need different type of permission?

I would appreciate if you aswer all the questions or give me explanation on the problem.

Thanks,
Erik T.