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

MrAutomateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
conrabaCommented:
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
0
 
MrAutomateAuthor Commented:
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

0
 
MrAutomateAuthor Commented:
Did some more research, if using integrated security, connection succeeds:
Use Integrated Security: No  = FAILS
Does this mean that there are not specify accoutns for each user setup as a SQL server login, and if so, i go back to my original question

Tnx


Microsoft SQL Server ODBC Driver Version 03.85.1132

Data Source Name: myDSN
Data Source Description: myDSN
Server: xxxxx
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.