Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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

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
P1
Asked:
P1
  • 8
  • 7
1 Solution
 
JHausmannCommented:
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
 
P1Author Commented:
JHausmann,

Same error, but no extra verbage.

Thanks, P1

Returns:
Run-Time error '-2147217843 (80040e4d)':
Login failed
0
 
JHausmannCommented:
Just looking at the string, is the typo "Sercurity" in your code?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
P1Author Commented:
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
 
P1Author Commented:
.... News Flash .....
With the above Typo Fixed, this does not work for either 6.5 or 7.0.

Regards, P1
0
 
JHausmannCommented:
<heh> Sorry, I appear to have made things worse...
0
 
P1Author Commented:
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
 
JHausmannCommented:
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
 
mdouganCommented:
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
 
JHausmannCommented:
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
 
JHausmannCommented:
<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
 
P1Author Commented:
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
 
P1Author Commented:
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
 
JHausmannCommented:
How do I get the other 100 points? Providing an answer here?
0
 
JHausmannCommented:
Nevr mind, thanks...
0
 
P1Author Commented:
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now