?
Solved

using SQLOLEDB from VBScript

Posted on 2002-05-20
5
Medium Priority
?
2,206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 300 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 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