Build an ODBC Connection String for SQL Azure in the Cloud
SQL Azure™ is built on Microsoft SQL Server® technologies and is designed to provide a highly-available and scalable database service hosted by Microsoft in the cloud. Developers who deploy databases in SQL Azure do not have to install, setup, patch, or manage any relational database software, only their own database structure and content. Automatic redundancy and fault tolerance are built-in and no physical administration is required.
You can build a manual connection string and use SQL Server syntax and datatypes to connect DatabaseSpy and other Altova tools to SQL Azure databases to perform typical database development and maintenance tasks. This tech note illustrates step by step how to build a connection string for DatabaseSpy to connect to an Azure database.
Before performing these steps, you will need a SQL Azure account, or a login and password created by a SQL Azure account holder. For more information on setting up a SQL Azure account, visit the Microsoft SQL Azure home page.
You will also need to install the Microsoft SQL Server Native Client 10.0 (or later).
Your First Connection From DatabaseSpy to SQL Azure
To begin, launch DatabaseSpy® 2017 and open the Connection dialog. SQL Azure does not behave exactly like a local SQL Server, so we cannot use the SQL Server Connection Wizard. Instead, we will build a new ODBC connection string. Select ODBC Connections in the left column and click the Build button.
(The steps below are illustrated using DatabaseSpy. The same database connection steps can be followed from XMLSpy, MapForce, or StyleVision to connect to a SQL Azure database in the cloud.)
The Select Data Source dialog opens next. For your very first connection, simply click the New button.
The Create New Data Source dialog opens, permitting you to choose the database driver for the new connection. Select the SQL Server Native Client 10.0 (or later), then click the Next button.
Choose a location and enter a file name for your new connection, click the Next button.
The next screen lets you add a description and specify the Azure server to connect to. You will be connecting to the cloud, so the server string will start with tcp: In place of “yourservername” illustrated below, enter the string that corresponds to your own SQL Azure server.
On the next screen, choose SQL Server authentication and enter your Login ID and Password, then click the Next button.
For your first connection, choose the master database. You must be connected to master to run certain SQL statements, such as CREATE DATABASE.
You can accept the defaults on the dialog that follows and click the Next button.
A summary of your selections is displayed.
The Select Data Source dialog is presented. This is the same dialog we saw at the beginning. This time the new data source is selected. Click the OK button.
Verify your Login ID, enter your Password, then click the OK button.
The connection string you created is displayed. Click the Connect button.
Since the behavior of SQL Azure varies slightly from Microsoft SQL Server 2008, choose the ODBC connection method and click OK.
Now you can enter a Data Source Name that will be displayed in the DatabaseSpy Project window. Click the OK button.
DatabaseSpy connects to your SQL Azure database. Your Data Source name is displayed in the DatabaseSpy Project Window.
The DatabaseSpy Properties Window shows your connection details. For best results, make sure the Use ODBC for retrieval option is checked, as shown below.
Congratulations on making your first connection from DatabaseSpy to SQL Azure. Before performing any operations on your SQL Azure database, you can save a new DatabaseSpy Project to preserve all your current connection settings for easy retrieval.
After you have created your first data source connection string to your SQL Azure database in one Altova application, as we did with DatabaseSpy above, you can reuse that connection string from other Altova tools.
For instance, if your SQL Azure database contains XML data, or if you want to generate an XML Schema from its relational data, you can connect with XMLSpy. When you click the Database Query button on the XMLSpy tool bar, the familiar Database Connection dialog opens.
You can click the Build button to open the Select Data Source dialog. Now you can select the SQL Azure DSN file originally created in DatabaseSpy and click OK.
The SQL Server Login dialog appears. Enter your Password and click the Options button. The Login dialog expands as displayed below.
The Database button in the SQL Server Login dialog is now a drop-down menu. You can make a connection to any database on your SQL Azure server that is authorized for your Login ID.
After you select a database and click OK, the XMLSpy Database Query dialog opens and you can perform database operations.