Solved

using SQLOLEDB from VBScript

Posted on 2002-05-20
5
2,145 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

15 Experts available now in Live!

Get 1:1 Help Now