MrAutomate
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?
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 "
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;init
End If