Solved

using SQLOLEDB from VBScript

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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