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
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:
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