"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.
LVL 4
P1Asked:
Who is Participating?
 
JHausmannConnect With a Mentor Commented:
<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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JHausmannCommented:
Just looking at the string, is the typo "Sercurity" in your code?
0
 
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
 
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
All Courses

From novice to tech pro — start learning today.