SQL Server - Creating an SQL Server ODBC Data Source

About

How to create an ODBC data source to connect to the SQL Server database using ODBC - Open Database Connectivity.

Articles Related

Steps

To do this, you must set up a System Data Source Name (DSN):

• Select Start, Control Panel, Administrative Tools, Data Sources (ODBC). This opens the ODBC Data Source Administrator dialog box.
• Navigate to the System DSN tab and click Add to open the Create New Data Source dialog box.
• Select SQL Server as the driver for which you want to set up the data source.
• Click Finish to open the Create A New Data Source to SQL Server Wizard.
• In the Name field, specify a name for the data source. For example, sqlsource.
• In the Server field, select the server to which you want to connect and click Next.
• Specify whether the authentication should be done at the Operating System level or at the server level. Click Next.
• Select the database file and click Next.
• Accept the default values in the next screen and click Finish.
• Test the data source to verify the connection

Connection String

• Connection String Syntax

Azure

• Turn on SSL EncryptionMethod=1 1=SSL
• Then:
  • Configure the Trust Store TrustStore=fullPath and TrustStorePassword=pwd if there is a pwd on the certificate.
  • or Eventually ValidateServerCertificate=0 No certification validation. Useful in test environments, no need to specify truststore information on each client

Doc:

• Product: Connect(64) for ODBC SQL Server Wire Protocol driver
• DataStage ODBC Connection to SQL Server failing due to missing SSL configuration

Example with SSL certificate turned off.

[sqlserver_azure]
Driver=/home/oracle/product/fmw/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib/ARsqls27.so
Description=Sql Server Azure
Address=server.database.windows.net
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=AdventureWorks
LoadBalancing=0
LogonID=
Password=
QuoteID=No
ReportCodePageConversionErrors=0
EncryptionMethod=1
ValidateServerCertificate=0