How to Create an ODBC Data Source Name (DSN) on Windows Azure Web/Worker Role

  • Article

Legacy applications frequently leverage ODBC connectivity to data sources in general and to the SQL Server in particular. ODBC is supported in Windows Azure with SQL Server 2008 /R2 Native Client ODBC driver. Consequently, it is possible to migrate such applications to Windows Azure Roles. However, if the legacy application uses Data Source Names (DSNs) and the source code is not available you may be faced with the problem of how to automate the creation of a DSN on the Web or Worker Role.

Because the Web and Worker Roles instances can be moved to a different hardware when Azure Host OS is updated, we need to ensure that the data source is automatically created for us.

In general, ODBC DSNs are created with the ODBC Data Source Administrator that stores information in Windows Registry. You can create System (accessible to all users) and User (accessible by a given user) DSNs. In our case we will focus on System DSNs.

To automate creation of the DSN on Azure Web/Worker Role we will use a startup task that will write to the Windows Registry the same information that the ODBC Data Source Administrator creates.

This article contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base: 322756 “How to back up and restore the registry in Windows”.

On the 64 bit OS ( Azure OS is 64bit) there are two locations where the configuration can take place depending if your code is 64 bit or 32 bit and therefore if it uses a DSN created with 32 bit (%SystemRoot%\SysWOW64\odbcad32.exe) or the 64 bit version (%windir%\system32\odbcad32.exe) of the ODBC Data Source Administrator.

On the 64 bit machine the former writes to the registry at [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI], whereas the latter writes to the key [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI].

image

Quick inspection with the Registry Editor (regedit.exe) shows that the key [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] holds the mapping from the DSN to the driver name. On the other hand, the key [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\DSN_ Name] holds the DSN configuration details.

The keys can be exported from the Registry Editor into a file that we will later use to create a DSN. For example consider the following 32 bit System DSN configuration odbc.reg file:

 Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLNativeAzure]
"Database"="CustomerDB"
"Description"="NerdDinner database in SQL Azure server"
"Driver"="%SystemRoot%\\system32\\sqlncli10.dll"
"LastUser"="user@ma9zkyiigg.database.windows.net"
"Server"="ma9zkyiigg.database.windows.net"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"SQLNativeAzure"="SQL Server Native Client 10.0"

Note, that in the “Driver” key we cannot use the absolute path to the driver because on the Windows Azure Web or the Worker role we do not know in advance the drive on which operating system files will be installed. Therefore, I use environment variable to work around that problem.

We can test our registry DSN configuration on the development machine running the command

regedit /s odbc.reg

The /s option suppresses informational dialog boxes that would normally be displayed. You should be able to verify with the ODBC Data Source Administrator that the DSN was correctly created.

What is left is to configure in your Visual Studio Azure solution the startup task to run the command.

The startup task must run in the elevated executionContext to ensure administrative permissions required for registry. Following is the example of the relevant part of the ServiceDefinition .csdef file:

 <ServiceDefinition name="MyService" xmlns="https://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition">
   <WebRole name="WebRole1">
      <Startup>
         <Task commandLine="regedit /s odbc.reg" executionContext="elevated" taskType="simple">
         </Task>
      </Startup>
   </WebRole>
</ServiceDefinition>

Obviously, you should not forget to add the odbc.reg file to the solution and mark the file properties in Visual Studio with Copy To Output Directory=Copy if Newer to ensure it forms part of the package that will be generated.

If you also configure Remote Desktop access to the role instance you can launch the ODBC Data Source Admin on the actual Role instance to confirm that the DSN is set up correctly.

At this stage an application can use the configured DSN in connection string as in the following example:

"DSN=SQLNativeAzure;uid=user@ma9zkyiigg.database.windows.net;pwd=my_password;"

You may be tempted to elaborate a more complex startup task to create the shortcut to the ODBC Data Source Administrator with the idea to use it later in the Remote Desktop (RD) session on the role instance. However, application shortcuts are associated with the user profiles and the Remote Desktop user profile most likely is not created yet when the startup task runs.

What you can do to work around this is to copy a script that creates the shortcut to a well- known folder. After logging to role instance via RD you would then run this script to get shortcuts created for you. But this is a topic for another blog.