Paul Tormey
asked on
VBSCRIPT sql odbc login failure
I'm running a VBScript that connects to an ODBC data source. The database, in this instance, is on a SQL 2000 server.
I have chosen to use ODBC and to execute an SQL command as I will be running a similar command against a separate INGRES DB.
The Open statement fails with the following message:
Microsoft OLE DB Provider for ODBC Drivers (38, 1) : [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''mydomain\myuserid'.
In the server event log I see that the userid is succesfully logged in to the system.
Event Type: Success Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 540
Date: 7/08/2008
Time: 11:03:20 a.m.
User: mydomain\myuserid
Computer: ServerName
Description:
Successful Network Logon:
User Name: myuserid
Domain: mydomain
Logon ID: (0x0,0xD8A19C0)
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: ETC64391
Logon GUID: -
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: -
Source Port: -
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
See the attached Code Snippet for failing and working code. But remember, I want to run native SQL commands against the relevant DB's.
Appreciate any help!
Paul
I have chosen to use ODBC and to execute an SQL command as I will be running a similar command against a separate INGRES DB.
The Open statement fails with the following message:
Microsoft OLE DB Provider for ODBC Drivers (38, 1) : [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''mydomain\myuserid'.
In the server event log I see that the userid is succesfully logged in to the system.
Event Type: Success Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 540
Date: 7/08/2008
Time: 11:03:20 a.m.
User: mydomain\myuserid
Computer: ServerName
Description:
Successful Network Logon:
User Name: myuserid
Domain: mydomain
Logon ID: (0x0,0xD8A19C0)
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: ETC64391
Logon GUID: -
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: -
Source Port: -
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
See the attached Code Snippet for failing and working code. But remember, I want to run native SQL commands against the relevant DB's.
Appreciate any help!
Paul
Dim oConn
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=MSDASQL;" & _
"Driver={SQL Server};" & _
"Server=ServerName;" & _
"Database=DBName;" & _
"Uid=mydomain\myuserid;" & _
"Pwd=mypassword"
oConn.Execute "Select count(*) from W6TASKS_TASKHISTORY"
Set oConn = Nothing
The following code executes successfully against the same DB on the same server:
strDBServerName = "ServerName"
strDBName = "DBName"
strDBReplicator = "ReplicatorCheck"
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
Set objSQLReplicator = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
Set objDB = objSQLServer.Databases(strDBName)
Set colTables = objDB.Tables
For Each objTable In colTables
If Left(objtable.Name,2) = "W6" Then
WScript.Echo "Table Name: " & objTable.Name
WScript.Echo "Num Rows: " & objTable.Rows
End If
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As per my earlier comment, this helped me get the connection working.
Thanks for the help.
Paul
Thanks for the help.
Paul
No worries - glad to help.
It *is* a wonderful trick, and I must credit my mentor for showing it to me. "Who is my mentor," you ask. Alas, the Alzheimers has captured that memory, and I haven't a clue...
It *is* a wonderful trick, and I must credit my mentor for showing it to me. "Who is my mentor," you ask. Alas, the Alzheimers has captured that memory, and I haven't a clue...
ASKER
Thanks for the info, very neat trick and looks very helpful.
In fact, it was very helpful.
Here is the connection string from the UDL file:
Provider=MSDASQL.1;Persist
As simple as that.