using SQLOLEDB from VBScript

I have SQL Server 2000 and IIS on one computer (w2k adv server). SQL server is working in Windows-only authentication mode.
I'm working on another computer (w2k pro).
Both computers are in the workgroup, not in the domain.
I'm administrator on both computers.
I created the database and some tables.
No questions about username and password.
I entered some data into tables using ODBC and MS Access.
Still no problems.
Then I opened the ASP script with the following code:
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB.1;" & _
  "Initial Catalog=MyDatabase;" & _
  "Data Source=THE_SERVER;" & _
  "Connect Timeout=15;"

This is the result:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'shvetsov'. Reason: Not associated with a trusted SQL Server connection.

I tried to add myself in SQL Server enterprise manager to security\Logins. It didn't help.
I tried to add "user Id=sa" to connection string. It didn't help.
What should I do?
Who is Participating?
crw030Connect With a Mentor Commented:
I'll look for the microsoft article, but running a clustered website I can tell you there is no way to run a website on IIS but access your backend SQL database using Windows Authentication.  Here's why:

IIS supports NT authentication.  So you CAN connect to your website using NT authentication (IIS will authenticate against itself and/or a domain controller, asking the browser via challenge response). **Note** the IIS server never has visibility to the user password, both the client and the IIS server create a HASH based on PWD and USERNAME and server supplied key..if they match your in!)  The problem is when IIS tries to connect on behalf of the user to a SQL server using NT challenge response.  The SQL server challenges the IIS server using the same mechanism (but a different server supplied key), and IIS tries to forward the HASH it receved from the client.  The connection will fail.  (or so I understand from extensive reading trying to get our clustered website up)

The solution:  Either IIS and SQL on the same box (using local pipes)...this obviously didnt work for us because we have a web cluster (multiple load balanced IIS servers...and a SQL failover cluster).....the other way is to use TCP combined with SQL authentication from IIS to SQL Server with NT authentication on the website to limit access to certain webpages to an NT management group.

I am happy to report this method works fine everytime (for over a year).

Here's some links:
If you allow for mixed authentication mode in SQL Server, the "User Id=sa;Password=mypassword;" would work.
shvetsovAuthor Commented:
But what about windows-only authentication mode?

I have done this without connecting to server machine's domain and without staying in same workgroup.
First u have to register the sqlserver in ur sql server client using administrator password.use windows Nt authentication.
Then should be able to connect sqlserver from ur vb script.And U should have same user and password in ur machine and server machine both.
Also I want to highlight that you MUST make sure IIS DOES NOT use named pipes (the default) to access your SQL Server.  I'll save you the $250 call to Microsoft and tell you once we switched to Mixed mode and started using a SQL password from IIS web pages that we moved from the SQL server (when it was also the IIS server) still didn't work because the IIS servers were using Named Pipes which according to Microsoft defaults to trying NT authentication.  (Bad for reasons described above)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.