Solved

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

Posted on 2000-03-10
16
259 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
[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
  • 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
Industry Leaders: 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!

 
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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

729 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