?
Solved

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

Posted on 2000-03-10
16
Medium Priority
?
261 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 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…
Suggested Courses

770 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