Create DSN for SQL Server with Username and Password
Hello,
Please i need help with how to automatically create DSN for connection to MSSQL server with supplied username and password.
I have searched online and cant find anything that works.
The following code i have used worked if i am not supplying username and password, but i need the one that works with supplied username and password
Code:
'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0 ' NULL Pointer
'Function Declare
#If Win32 Then
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) _
As Long
#Else
Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
(ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If
'//Add DSN
Private Sub Command1_Click()
#If Win32 Then
Dim intRet As Long
#Else
Dim intRet As Integer
#End If
Dim strDriver As String
Dim strAttributes As String
strDriver = "SQL Server"
strAttributes = "SERVER=MSSQLSERVER1" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
strAttributes = strAttributes & "DATABASE=MonoDB" & Chr$(0)
strAttributes = strAttributes & "Trusted_Connection=true" & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
strDriver, strAttributes)
If intRet Then
MsgBox "DSN Created"
Call TestDSNConnection("DSN_TEMP")
Else
MsgBox "Create Failed"
End If
End Sub
Please help.
Re: Create DSN for SQL Server with Username and Password
"Trusted_Connection" is not a valid keyword for SQLConfigDataSource in some driver versions—the DSN does not store the authentication method here. Remove the "Trusted_Connection" line entirely and pass the UID/PWD only at connect time (in TestDSNConnection), not in the DSN attributes.
SQLConfigDataSource does not store the password in the DSN (credentials aren't persisted by design), so you supply them at connect time.
Code:
Option Explicit
'Constant Declaration
Private Const ODBC_ADD_DSN = 1
Private Const ODBC_CONFIG_DSN = 2
Private Const ODBC_REMOVE_DSN = 3
Private Const vbAPINull As Long = 0
#If Win32 Then
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
#Else
Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
(ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If
Private Declare Function SQLInstallerError Lib "odbccp32.dll" _
(ByVal iError As Integer, pfErrorCode As Long, _
ByVal lpszErrorMsg As String, ByVal cbErrorMsgMax As Integer, _
pcbErrorMsg As Integer) As Integer
Private Declare Function SQLGetPrivateProfileString Lib "odbccp32.dll" _
(ByVal lpszSection As String, ByVal lpszEntry As String, _
ByVal lpszDefault As String, ByVal RetBuffer As String, _
ByVal cbRetBuffer As Long, ByVal lpszFilename As String) As Long
Private Function DSNExists(ByVal dsnName As String) As Boolean
Dim sBuf As String * 256
Dim nRet As Long
' Read the list of DSNs in the "ODBC Data Sources" section
nRet = SQLGetPrivateProfileString("ODBC Data Sources", dsnName, "", _
sBuf, 256, "ODBC.INI")
DSNExists = (nRet > 0)
End Function
Private Sub ShowODBCError()
Dim sMsg As String * 512
Dim lErr As Long
Dim iLen As Integer
Dim r As Integer
r = SQLInstallerError(1, lErr, sMsg, 512, iLen)
If iLen > 0 Then
MsgBox "ODBC error " & lErr & ": " & Left$(sMsg, iLen)
Else
MsgBox "No ODBC installer error info"
End If
End Sub
'//Add DSN
Private Sub Command1_Click()
Const DSN_NAME = "DSN_TEMP"
If DSNExists(DSN_NAME) Then
Dim r As VbMsgBoxResult
r = MsgBox(DSN_NAME & " already exists. I'm recreating it.?", vbYesNo + vbQuestion)
If r = vbNo Then Exit Sub
' Optional: Wipe it down first
Call SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _
"SQL Server", "DSN=" & DSN_NAME & Chr$(0) & Chr$(0))
End If
#If Win32 Then
Dim intRet As Long
#Else
Dim intRet As Integer
#End If
Dim strDriver As String
Dim strAttributes As String
Dim strUID As String
Dim strPWD As String
strUID = "user" ' supplied username
strPWD = "pass" ' supplied password
strDriver = "SQL Server"
strAttributes = "SERVER=SERVER\ACE" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Temporary SQLServer DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
strAttributes = strAttributes & "DATABASE=TermEX" & Chr$(0)
strAttributes = strAttributes & Chr$(0)
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
strDriver, strAttributes)
If intRet Then
MsgBox "DSN Created"
Call TestDSNConnection("DSN_TEMP", strUID, strPWD)
Else
MsgBox "Create Failed"
Call ShowODBCError
End If
End Sub
Private Sub TestDSNConnection(ByVal dsnName As String, _
ByVal uid As String, ByVal pwd As String)
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
On Error GoTo ConnErr
cn.Open "DSN=" & dsnName & ";UID=" & uid & ";PWD=" & pwd & ";"
MsgBox "Connection OK"
cn.Close
Set cn = Nothing
Exit Sub
ConnErr:
MsgBox "Connection failed: " & Err.Description
End Sub
1. UID/PWD written into the attribute string only set defaults that the DSN may prompt for later — they are not saved in the registry as the password, so always pass UID/PWD again at cn.Open.
2. If you're on a new SQL Server, consider strDriver = "ODBC Driver 17 for SQL Server" (or 18) instead of the legacy "SQL Server"
3. First check if DSN exist in ODBC32 and maybe delete
