Solved

"Login failed" on SQL Server 6.5, but not SQL Server 7.0

Posted on 2000-03-10
16
254 Views
Last Modified: 2013-12-25
What should I be looking for on the SQL 6.5 server to get this to work?


'DSN-Less connection.
g_SQL_cn.ConnectionString = "DRIVER={SQL Server};Connect Timeout=240;SERVER=My_Server;DATABASE=My_DB;Integrated Sercurity=True;DSN="

Returns in a pop-up:

Run-Time error '-2147217843 (80040e4d)':
[Microsoft][ODBC SQL Server Driver][SQL Driver] Login failed

This code runs fine against SQL Server 7.0.

I am in the Administrator Group, With a Login in Security Manager, which is in Enterprise Manager, with All rights.
0
Comment
Question by:P1
  • 8
  • 7
16 Comments
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605643
You might try:

g_SQL_cn.ConnectionString = "DRIVER={SQL Server};Connect Timeout=240;SERVER=My_Server;DATABASE=My_DB;Integrated Sercurity=True;provider=SQLOLEDB"

My docs indicate if you don't specify a provider, the connection defaults to MSDASQL (Microsoft OLE DB provider for ODBC)

0
 
LVL 4

Author Comment

by:P1
ID: 2605675
JHausmann,

Same error, but no extra verbage.

Thanks, P1

Returns:
Run-Time error '-2147217843 (80040e4d)':
Login failed
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605694
Just looking at the string, is the typo "Sercurity" in your code?
0
 
LVL 4

Author Comment

by:P1
ID: 2605762
JHausmann,

Returns:
Run-Time error '-2147217887 (80040e21)':
Errors Occurred

I will award 100 points.  For getting me this far.  Look for a question with your ID.

Thanks, P1

0
 
LVL 4

Author Comment

by:P1
ID: 2605798
.... News Flash .....
With the above Typo Fixed, this does not work for either 6.5 or 7.0.

Regards, P1
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605821
<heh> Sorry, I appear to have made things worse...
0
 
LVL 4

Author Comment

by:P1
ID: 2605838
JHausmann,

Then I suggest that you don't into the spell check business.  ;-)

Progress is not a destination, but a journey to an end.

Thanks, P1
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2605956
When I created a data environment, setting the values on the connection to one of my servers (to approximate yours), I get a value of "SSPI" (without the quotes) for integrated servers.

If you use the "provider" option, you probably will want to change "Server=" to "Data Source=" and "Database=" to "Initial Catalog="...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:mdougan
ID: 2606104
Either of these will work:

Option Explicit
Dim CNMSSQL As ADODB.Connection
Dim RS As ADODB.Recordset

Private Sub Command1_Click()
Dim STRCONNECTION As String

    Set CNMSSQL = New ADODB.Connection
    With CNMSSQL
        .ConnectionTimeout = 3
        .CursorLocation = adUseClient
    End With
       
    STRCONNECTION = "DRIVER={SQL Server};UID=youruid;PWD=yourpwd;SERVER=yourDB01;DATABASE=yourdatabase"
   
    CNMSSQL.Open STRCONNECTION
   
    Set RS = CNMSSQL.Execute("Select * from COMPANY")
    Me.Cls
    While Not RS.EOF
        Me.Print RS.Fields(1).Value
        RS.MoveNext
   Wend
RS.Close

End Sub

Private Sub Command2_Click()
Dim STRCONNECTION As String

    Set CNMSSQL = New ADODB.Connection
    With CNMSSQL
        .ConnectionTimeout = 3
        .CursorLocation = adUseClient
        .Provider = "SQLOLEDB"
    End With
       
   STRCONNECTION = "Provider=SQLOLEDB;SERVER=yourDB01;" & _
                "User ID=youruid;Password=yourpwd;"
               
    CNMSSQL.Open STRCONNECTION
   
    Set RS = CNMSSQL.Execute("Select * from COMPANY")
    Me.Cls
    While Not RS.EOF
        Me.Print RS.Fields(1).Value
        RS.MoveNext
   Wend
RS.Close

End Sub
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2606129
P1's attempting to use NT Domain and SQL server to authenticate the logon (using the integrated security feature). IOW, he's not intending on providing a user name or password.
0
 
LVL 2

Accepted Solution

by:
JHausmann earned 200 total points
ID: 2606160
<sigh> another typo. "SSPI" is the value for Integrated Security. I need a vacation.

WRT my last post, is there any difference (other than the obvious name change) between Integrated Security and Trusted Connections?
0
 
LVL 4

Author Comment

by:P1
ID: 2606168
Thanks goes to JHausmann, be sure to pick the extra 100 points in this QNA list.

I had a Microsoft Press book give a True/False parameters for Integrated Security.

'Problem String
..ConnectionString = "DRIVER={SQL Server};Connect Timeout=240;SERVER=My_Server;DATABASE=My_DB;Integrated Sercurity=True;DSN="

'Working ConnectionString
..ConnectionString = "DRIVER={SQL Server};SERVER=My_Server;DATABASE=My_DB;Integrated Security=SSPI;Provider=SQLOLEDB;DSN="

mdougan,

Thanks for your solution.  The solution that best fits the programming task I had to have Integrated Security with a DSN-less connection.

P1
0
 
LVL 4

Author Comment

by:P1
ID: 2606187
Thanks for sticking with it, after the typo fix.
Be sure to get your other 100 points, under your name on this QNA list.
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2606195
How do I get the other 100 points? Providing an answer here?
0
 
LVL 2

Expert Comment

by:JHausmann
ID: 2606198
Nevr mind, thanks...
0
 
LVL 4

Author Comment

by:P1
ID: 2606233
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DIR issue 7 51
Vb.net 2008 2 56
How to debug this code 7 56
to transfer string from C lanaguage to VBA 4 58
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 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

22 Experts available now in Live!

Get 1:1 Help Now