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

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.

From stackoverflow

• 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.

  Matthew Murdoch : I don't seem to have this exe.

  Bravax : Sorry I spelled it incorrectly it's odbcad32.exe

  Matthew Murdoch : Unfortunately I need a programmatic way of doing this (this is the UI that I can access via 'Administrative Tools'. I have updated the question to be clear.

• 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
  

• 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)
  

  Matthew Murdoch : I specifically want to create a SQL Server Data Source. This is a general mechanism for creating Data Sources which doesn't give me much beyond accessing the Registry directly.

  Pittsburgh DBA : Matthew - this does give you what you want. Just look further into the documentation and examples.

  Matthew Murdoch : Could you point me to a specific example?

  Pittsburgh DBA : Here is one. Creating DSNs from code: http://www.mvps.org/access/tables/tbl0014.htm I still think you should accept Sergey's answer.

  Matthew Murdoch : Have done, thanks!

• 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
  

• 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);
  
          }
  

  djangofan : Using a Type 4 driver would be much better than a ODBC DSN and probably easier. Using System.Data.SqlClient would be much better.