Results 1 to 9 of 9

Thread: Connection string for Integrated Security

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    419

    Connection string for Integrated Security

    Hello:
    I am trying to connection using old school VBA.

    This works in VBA!
    Code:
    connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
    This works in VB.NET, but says the server does not exist or access is denied...
    Code:
    "Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
    Why would the server be available in VB.NET but not in VBA?

    Here's the complete code. I must be missing something..
    Code:
    Sub GetJobs_Click()
        Dim connectionString As String
        ' connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
        connectionString = "Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
        Dim cn As ADODB.Connection
        Set cn = New ADODB.Connection
            
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
            
        Dim sql As String
        sql = "SELECT * From [JobSheet].[dbo].[JobInfo] ORDER BY JobNo "
        MsgBox (sql)
            
        cn.connectionString = connectionString
        cn.Open ' ERROR'S HERE
            
        rs.Open sql, cn, adOpenKeyset, adLockReadOnly, adCmdText
        rs.MoveFirst
        Do While Not rs.EOF
            MsgBox (rs.Fields("JobNo"))
            rs.MoveNext
    
        Loop
    
        rs.Close
        Set rs = Nothing
            
        cn.Close
        Set cn = Nothing
    
    End Sub
    Thanks!
    Last edited by ssabc; Oct 13th, 2020 at 11:44 AM.
    -SA

  2. #2
    Frenzied Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    1,279

    Re: Connection string for Integrated Security

    You have different Data Source names in those two connections strings - do both of those servers exist?

    Also when you are using integrated security then the person running the application would need a user account and appropriate permissions in the target server / database.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    419

    Re: Connection string for Integrated Security

    The first cn is remmed out. It is an example showing what works on a completely different dataset.

    The second cn uses Integrated Security, is the one I need, and is not working.
    -SA

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    419

    Re: Connection string for Integrated Security

    Oh yes, we have permissions in the Db. As I mentioned, the Integrated Security string works in VB.NET but not in VBA.
    -SA

  5. #5
    Lively Member
    Join Date
    Jul 2020
    Posts
    95

    Re: Connection string for Integrated Security

    And if so?
    Code:
    Public conn As New ADODB.Connection                 'Variables for working with the database
    Public rs As New ADODB.Recordset
    '...................
        Set conn = New ADODB.Connection
    conn ="Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
        If conn.State <> 1 Then
            MsgBox "Database connection not established!", vbOKOnly + vbCritical, "Error!"
            Exit Sub
        End If

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    419

    Re: Connection string for Integrated Security

    cn=0. That's not a surprise.

    Am I missing references? The think that kills me is that this works:
    Code:
    connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
    Of course I don't want this, I want this:
    Code:
    connectionString = "Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
    Here's the database image for reference. There's got to be something wrong with the syntax. Though the identical syntax works fine in VB.NET 2017 with the same database permissions and user login.
    Attachment 179008
    -SA

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,343

    Re: Connection string for Integrated Security

    Those two have different sources as well, have you tried using the same data source in both strings to see if you get different results?

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    419

    Re: Connection string for Integrated Security

    The solution is!!:

    Code:
    connectionString = "Provider=sqloledb;Data Source=sage/SQLEXPRESS;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
    Because the other server has two instances of SQL Server
    -SA

  9. #9

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width