Solved

using SQLOLEDB from VBScript

Posted on 2002-05-20
5
2,150 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:shvetsov
5 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 7023241
If you allow for mixed authentication mode in SQL Server, the "User Id=sa;Password=mypassword;" would work.
0
 

Author Comment

by:shvetsov
ID: 7023439
But what about windows-only authentication mode?
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7029251
Hi.,

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.
0
 
LVL 1

Accepted Solution

by:
crw030 earned 100 total points
ID: 7051827
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:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsentpro/html/veconchoosingsqlserverauthenticationmethod.asp>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/deploydap.asp>
0
 
LVL 1

Expert Comment

by:crw030
ID: 7051843
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)
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now