Connect to a Microsoft SQL database

 Top Previous Topic Next Topic  Print this topic

Connecting to a SQL database in Data Architect requires the user to have a Microsoft SQL Server installed on the computer.

 

1. Connecting to a local database

To add a connection to a SQL database, the user must select the Add Data Connection button add_data_connection_button, located in the Data Connections dialog bar or right click on the Data Connections root and select the Add Data Connection option. As a result, the Add Connection dialog will be displayed.

 

sql_connection2

 

•Connection Name - the name given by the user to identify the connection.
•Provider Type - Microsoft SQL Server must be selected, as displayed below.
 

sql_connection1

 

•Server Name - the name of the host and the instance of the SQL Server must be inserted (for example, localhost\SQLEXPRESS, where "localhost" is the name of the host and SQLEXPRESS is the instance of the SQL server).
•Database - the name of the database to be accessed from the defined server must be inserted. (in this example, "Northwind").
•Log on to the server - The user can log on to the server by using either Windows Authentication or SQL Server Authentication (in which case the user credentials must be provided).

 

Note:

•The server must be set to the correspondent authentication mode. In order to do this, the Microsoft SQL Server Management Studio Express must be opened. Then, the user must right click on the server name, located in the Object Explorer, and select Properties. In the Server Properties dialog, the user must select the authentication mode from the Security menu, as displayed below.

 

sql_connection3

 

 

2. Connecting to a remote database

To connect to a remote database, the steps above must be followed, except for the Server Name section, where the IP address/name of the server and the instance of the SQL Server must be introduced in the following format:

"IP_address\instance_of_SQL_Server".

 

The example below configures a connection named "Connection2" to the "Northwind" database located on the server with the 192.168.1.110 IP address. The server has an SQLEXPRESS instance of the SQL Server installed. To log on to the server, the SQL Server Authentication has been used.

 

sql_connection5

 

It is mandatory that the SQL Server is set to listen for remote client connections over TCP/IP or other protocols. In order to do this, the Surface Area Configuration for Services and Connections configuration tool must be opened and the Remote Connections option located under the Database Engine must be chosen.

Then, the protocol intended for usage must be configured, as displayed below.

 

sql_connection4