What is the simplest, most maintainable way to create a SQL Server ODBC Data Source?

Question

I need a programmatic way of creating a SQL Server ODBC Data Source. I can do this by directly accessing the Registry. It would be better if this could be done via an available (SQL Server/Windows) API to protect against changes in the registry keys or values with updated SQL Server drivers.

Accepted Answer Note: Using SQLConfigDataSource abstracts the code from the details of Registry keys etc. so this is more robust. I was hoping, however, that SQL Server would have wrapped this with a higher level function which took strongly typed attributes (rather than a delimited string) and exposed it through the driver.

Answer 1

I'd use odbcad32.exe which is located in your system32 folder.

This will add your odbc data sources to the correcct location, which won't be effected by any patches.

Answer 2

To do this directly in the registry you can add a String Value to:

HKLM\SOFTWARE\Microsoft\ODBC\ODBC.INI\ODBC Data Sources

to add a System DSN, or:

HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources

to add a User DSN.

The Name of the Value is the name of the Data Source you want to create and the Data must be 'SQL Server'.

At the same level as 'ODBC Data Sources' in the Registry create a Key with the name of the Data Source you want to create.

This key needs the following String Values:

Database     - Name of default database to which to connect
Description  - A description of the Data Source
Driver       - C:\WINDOWS\system32\SQLSRV32.dll
LastUser     - Name of a database user (e.g. sa)
Server       - Hostname of machine on which database resides

For example, using the reg.exe application from the command line to add a User Data Source called 'ExampleDSN':

reg add "HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources" 
    /v ExampleDSN /t REG_SZ /d "SQL Server"
reg add HKCU\Software\ODBC\ExampleDSN 
    /v Database /t REG_SZ /d ExampleDSN
reg add HKCU\Software\ODBC\ExampleDSN 
    /v Description /t REG_SZ /d "An Example Data Source"
reg add HKCU\Software\ODBC\ExampleDSN
    /v Driver /t REG_SZ /d "C:\WINDOWS\system32\SQLSRV32.DLL"
reg add HKCU\Software\ODBC\ExampleDSN
    /v LastUser /t REG_SZ /d sa
reg add HKCU\Software\ODBC\ExampleDSN
    /v Server /t REG_SZ /d localhost

Answer 3

SQLConfigDataSource() does the job.

MSDN article

Just in case here is a VB6 example:

Const ODBC_ADD_DSN = 1 'user data source
Const ODBC_ADD_SYS_DSN = 4 'system data source

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

strDriver = "SQL Server"
strAttributes = "DSN=Sample" & Chr$(0) _
& "Database=Northwind" & Chr$(0) _
& "Description= Sample Data Source" & Chr$(0) _
& "Server=(local)" & Chr$(0) _
& "Trusted_Connection=No" & Chr$(0)

SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strDriver, strAttributes)

Answer 4

For VB.NET it can be done this way:

Import for 'DllImport':

Imports System.Runtime.InteropServices

Declaration of SQLConfigDataSource:

<DllImport("ODBCCP32.DLL")> Shared Function SQLConfigDataSource _
(ByVal hwndParent As Integer, ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Boolean
End Function

Example usage:

Const ODBC_ADD_DSN = 1 'User data source
Const ODBC_ADD_SYS_DSN = 4 'System data source

Public Function CreateSqlServerDataSource
    Dim strDriver As String : strDriver = "SQL Server"
    Dim strAttributes As String : strAttributes = _
        "DSN=Sample" & Chr(0) & _
        "Database=Northwind" & Chr(0) & _
        "Description= Sample Data Source" & Chr(0) & _
        "Server=(local)" & Chr(0) & _
        "Trusted_Connection=No" & Chr(0)

    SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
End Function

Answer 5

Sample Using C#:

( Detailed SQL Server param reference at http://msdn.microsoft.com/en-us/library/aa177860.aspx )

using System.Runtime.InteropServices; 

        private enum RequestFlags : int
        {

            ODBC_ADD_DSN = 1,
            ODBC_CONFIG_DSN = 2,
            ODBC_REMOVE_DSN = 3,
            ODBC_ADD_SYS_DSN = 4,
            ODBC_CONFIG_SYS_DSN = 5,
            ODBC_REMOVE_SYS_DSN = 6,
            ODBC_REMOVE_DEFAULT_DSN = 7

        }

        [DllImport("ODBCCP32.DLL", CharSet = CharSet.Unicode, SetLastError = true)]
        private static extern bool SQLConfigDataSource(UInt32 hwndParent, RequestFlags  fRequest, 
                                 string lpszDriver, string lpszAttributes);

        public static void CreateDSN()
        {

            string strDrivername = "SQL Server";
            string strConfig =  "DSN=StackOverflow\0" +
                                   "Database=Northwind\0" +
                                   "Description=StackOverflow Sample\0" +
                                   "Server=(local)\0" +
                                   "Trusted_Connection=No\0";

            bool success = SQLConfigDataSource(0, RequestFlags.ODBC_ADD_SYS_DSN, strDrivername, strConfig);

        }