Remote DSN Connections, using WinAPIs and the registry

[ntsa]

Thanks again to all those who left comments and greenies on my last two tutorials (see links at end) Here's the latest one in which I will do my level best to explain how to use windows APIs to programatically connect to a remote DSN. If having read the article you find it useful, or just appreciate the effort, I appreciate all comments and greenies - it makes sitting here writing them all worthwhile

Ok - here we go - smoke 'em if you got 'em.

When writing IP based client server database apps it is often easiest to get the client to use ODBC to connect to a remote SQL server. But that means getting the client software to configure the ODBC connection to the remote server. All the information about ODBC connections is stored in the registry and so by using standard windows APIs we can write a bit of code that adds the relevant registry keys and will allow our client application to connect to our SQL server.

Because this code uses the windows API it could be re-written in most languages - I'm cutting and pasting here from one of my own VB aps, so the code is in VB.

    Private Const REG_SZ = 1    'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002
    Private Const HKEY_CURRENT_USER = &H80000001
    
    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
    
Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" ( _
           ByVal hKey As Long, _
           ByVal lpSubKey As String, _
           phkResult As Long _
) As Long

<NB>I'm not claiming credit for this bit of the code - you can get it from 100 sites. These are standard API declarations that I include for completeness. I just can't credit anyone here because I don't remember where I got it. Those who want a 'further reading' link: may I suggest http://msdn.microsoft.com </NB>

Ok - So now we have declared the APIs that we are going to use let's have an introduction to the registry keys that we are about to change and what they actually do.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\

This is where the meat of the ODBC settings reside. What we need to do is create a new sub key in here with the name of our DSN. Then we can add new key pairs to our DSN entry. It is here we specify the basic properties for the DSN including, Name, description, driver type, last user, and the alias of the server. The server alias refers to the MSSQLServer\Client\ConnectTo\ key that is discussed below. Each DSN has a sub-tree containing these variables.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\TDS\

TDS stands for Tabular Data Stream. This setting defines the packet header format of the data communication between client and server.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\

This is the key that contains the list of ODBC data sources for the ODBC appelet in the control panel. If you don't want to make your datasource visible to the user simply omit the entries from the code below that relate to this key. The database will still function normally, but the users will not be able to configure the DNS from the control panel. There are pro's and cons to making the DNS invisible - it depends on your application type and what you intend to use it for as to whether you decide to include or omit the changes to this key.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\

This key contains the actual connection information for the alias we created in ODBC\ODBC.INI\ key. In the case of MSSQL you need the Ip address and the port number that your server runs on. By default this port is 1433. So the value we will actually pass to the registry will be: "DBMSSOCN,xxx.xxx.xxx.xxx,1433". The DBMSSSOCN refers to DataBase Management System SOcket CoNection, ie a connection to a database via the net, the xxx's are the IP address on the server and the 1433 refers to the port to connect to. These values are then comma sepearted and passed as a string. Many DSNs can use the same alias btw.

Notes on using Non MSSQL DSNs

This actual information that we pass to the registry will be different for every type of SQL server, MySql, Prostgre etc. My example listed below will only work on MSSQL server. So the easiest way to figure out what the correct settings is for your flavour of SQL is to configure a client manually via the ODBC apelet in the control panel and then use regedit to check the values of the keys listed above.

Here's a tip to make life a bit easier for yourself however. Export the HKEY_LOCAL_MACHINE\SOFTWARE\ key to a regedit4 file BEFORE you set up your DSN through the control panel. Then you just have to double click on the exported file to return your test environment to it's previous state. This way you can still test your configuration program without picking up any settings you created through the control panel. If you are not using MSSQL you can use this tactic to take before and after snapshots of your registry to track what changes you need to make. That's what I did

The Code!

Again you'll have to change this a bit if you are using a language other than VB - but the principle is the same and so I include the code here that I wrote for VB/MSSQL remote DSN configuration.

Public Sub ConfigureDSN()

   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, lngHandle As Long

   'Specify the DSN parameters.

   DataSourceName = "DSNnameToCreate"
   DatabaseName = "NameOfSQLDatabase"
   Description = "A description of your DSN"
   DriverPath = "%systemroot%\System32\sqlsrv32.dll"
   LastUser = "LastUserName"
   Server = "xxx.xxx.xxx.xxx"
   DriverName = "SQL Server"

   'Create the new DSN key.

   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))
   
   'ADD TDS entry
   'Create tabular Data stream parameter for DSN

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
   "SOFTWARE\Microsoft\MSSQLServer\Client\TDS\", hKeyHandle)

   'Set the values of the new DSN key.

   lResult = RegSetValueEx(hKeyHandle, _
   "xxx.xxx.xxx.xxx", 0&, REG_SZ, _
   ByVal "7.0", Len("7.0"))


    'Add to control panel
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
   "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\", hKeyHandle)
    
    lResult = RegSetValueEx(hKeyHandle, "postitup", 0&, REG_SZ, _
     ByVal "SQL Server", Len("SQL Server"))
   
    
    'Add Connection Details for alias
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
    "SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\", hKeyHandle)
    
    lResult = RegSetValueEx(hKeyHandle, "xxx.xxx.xxx.xxx", 0&, REG_SZ, _
    ByVal "DBMSSOCN,xxx.xxx.xxx.xxx,1433", Len("DBMSSOCN,xxx.xxx.xxx.xxx,1433"))

    
    lResult = RegCloseKey(hKeyHandle)

End Sub

If you liked this article take a look at these:
Securing an installation of IIS 4. (No, seriously)
Scripting Internet Connections Under Window$

[prodikal]

i absouloutley love reading tutorials u always learn something new (well i do anyway)

[Seeker_319]

Information is always welcome in my opinion either as a tutorial or a refresher/reference

[jethro]

fl00t! Another smashing tutorial ntsa (aka. Mr. Carson )