Link to home
Start Free TrialLog in
Avatar of MrAutomate
MrAutomateFlag for United States of America

asked on

How to use dsn less connection

I am using the code below which works fine.  My problem is this.  I want to provide users with the ability to run monthly reports themselves, so my department does NOT need to run them.

I have setup a process that inserts my credentials into the equation in the attached code.

The problem...

it appears that when this DSN creates itself, it selects SQL authentication, and does not use NT Authentication.  

As soon as I change away from NT authentication, even if I enter a valid set of network credentials, the communication test fails.  

Our setup appears to only allow trusted connections.  This would require that anyone that may need to run reports , would require formal access to the databases.

How can I trick the users computer to tell SQL to use credentials I insert into the vba code.  Not being a SQL expert, it appears that NT Authentication, and SQL authentication are not equivlient.

Any ideas?
Option Compare Database

'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
    
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=No"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr
    End If
    
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
        
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:
    
    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
    
End Function

*******  I then have an autoexec macro that created the variables  , below:

Function AutoExec()
On Error GoTo Copy_of_AutoExec_Err

Dim UID As String
Dim PW As String

UID = DLookup("Test", "Table1")
PW = DLookup("Test1", "Table1")
    
    Call CreateDSNConnection("VA018D21", "D_STMEDB", UID, PW)
    Beep
    
    MsgBox "DSN connection has been established!", vbOKOnly, "DSN Connection "

Open in new window

Avatar of conraba
conraba
Flag of United States of America image

Use a SQLServer User Account instead of a Windows account for connectivity of your code.
try the snippet below and replace or pass appropriate values for connection

If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=No"
    Else
        stConnect = "data source=servernamehere;initial catalog=databasenamehere;User Id=SQLUserNamehere;Password=sqlpasswordhere;"
    End If
Avatar of MrAutomate

ASKER

Conraba,

I have applied your code, and I am not getting a sucesfull connection.  Am I mising quotes or something?  
When I run MsgBox stConnect ' verify all items are being passed, I get this:
Data source=VA018D21;initial catalog=D_STMEDB;User ID=UID;Password=PW;
Even if I hard code my credenitals in the string you provided, I get an odbc call failed.

Please see new code attached.  

Thanks!
Option Compare Database

'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection2() As Boolean

    On Error GoTo CreateDSNConnection_Err
Dim UID As String
Dim PW As String

UID = DLookup("Test", "Table1")
PW = DLookup("Test1", "Table1")

MsgBox UID ' verify UID values are passing
MsgBox PW  ' verify PW values are passing
    Dim stConnect As String
    
    stConnect = "data source=VA018D21;initial catalog=D_STMEDB;User Id=UID;Password=PW;"
    
    MsgBox stConnect ' verify all items are being passed
    
    DBEngine.RegisterDatabase "mysql", "SQL Server", True, stConnect

        
    '// Add error checking.
    CreateDSNConnection2 = True
    Exit Function
CreateDSNConnection_Err:
    
    CreateDSNConnection2 = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of MrAutomate
MrAutomate
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial