How to add MS SQL server as an ODBC data source?

ODBC stands for Open Database Connectivity. It is an API that allows you to access database systems. This connection was developed with an aim to make it independent of database systems and operating systems.

In this quick guide, we will see how a connection can be established to a Microsoft SQL server on your network.

What you need:

• A Microsoft SQL Server (MS SQL) on the network.
  • In this case, we will be connection to a server running Microsoft SQL Server 2005.
• A normal PC on the network.
  • In this example, I'll be using a workstation running Windows 7.

Step 1:

Click the Start button. Either navigate to Administrative Tools > Data Sources (ODBC) or simply search for ODBC.

  

Step 2:

In the ODBC Data Source Administrator window, click Add.

Step 3:

Since we are going to be connecting to MS SQL Server 2005, select SQL server in the Create New Data Source window. Click Next.

Step 4:

Type a Name for your data source. Next, enter the Server hostname or IP address. Click Next.

Step 5:

Select With SQL Server authentication using a login ID and password entered by the user.

Enter the Login ID and Password for your SQL server. Click Next.

Step 6:

Leave all settings as default and click Next.

Step 7:

Leave all settings as default and click Finish.

Step 8:

A window will pop up asking you whether you want to test the connection or not. Click Test Data Source… If all goes well, you should see a TESTS COMPLETED SUCCESSFULLY! message. Click OK.

Step 10:

Your new ODBC data source has been successfully added. Now you can use this data source in different applications to connect and access data from your SQL server.

Further reading

https://msdn.microsoft.com/en-us/library/ms811006.aspx

(header image source: thebigdatainsightgroup.com)