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.

how-to-add-odbc-data-source_02  How to add ODBC data source - Microsoft SQL Server

Step 2:

In the ODBC Data Source Administrator window, click Add.

how-to-add-odbc-data-source_03

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.

how-to-add-odbc-data-source_04

Step 4:

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

how-to-add-odbc-data-source_05

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.

how-to-add-odbc-data-source_06

Step 6:

Leave all settings as default and click Next.

how-to-add-odbc-data-source_07

Step 7:

Leave all settings as default and click Finish.

how-to-add-odbc-data-source_08

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.

how-to-add-odbc-data-source_09

how-to-add-odbc-data-source_10

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.

how-to-add-odbc-data-source_11

Further reading

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

(header image source: thebigdatainsightgroup.com)

Leave a Comment