Using VBA to create a System DSN with Windows NT authentication

rnodern

New member
Local time
Today, 15:18
Joined
Oct 20, 2010
Messages
5
Hi all,

I have been trying to create a System DSN via VBA. The problem is, I can't find the way to select "With Windows NT authentication using the network login ID"

For investigation purposes, I have created 2 DSN's manually in ODBC Data Source Administrator, using the two authentication methods (i.e. SQL Server authentication & Windows NT authentication). I then, went through the registry to identify the difference in the reg key entries. There appears to be nothing different.

Here is the code i am using:
Code:
    Private Const REG_SZ = 1    'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
       "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
       phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
       "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
       ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
       cbData As Long) As Long

    Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long


Code:
Function CreateDSN()

   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String

   Dim lResult As Long
   Dim hKeyHandle As Long
On Error GoTo Error1
   'Specify the DSN parameters.
   DataSourceName = "Data_Source_Name" 'I've changed this for demonstration purposes
   DatabaseName = "Database_Name"
   Description = "Description_Name"
   DriverPath = "C:\WINDOWS\system32"
   LastUser = "User_Name" 'I've tried not including this line, but it doesn't make a difference 
   Server = "SERVER_IP_OR_DNS"
   DriverName = "SQL Server"

   'Create the new DSN key in said registry.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.
   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))

   'Close the new DSN key.

   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)


'MsgBox "Install Complete!"
Exit Function

Error1:

MsgBox Err.Description & vbNewLine & "You may not have sufficient access to create an ODBC connection. This will need to be setup manually."


End Function

Any help out there?

Thanks,
rnodern
 
Perhaps you need the ODBC connection for something else but have you considered using DSN-less connections in your databse if that is what it is for?
 
I figured it out. You need to add a line in for "trusted connection".. so adding the following where specifying the DSN parameters worked.:

Trusted_Connection=Yes
 

Users who are viewing this thread

Back
Top Bottom