The purpose of this document is to describe how to create connection string for OLE DB provider and SQL Server native clients. Connection string is used to establish connection with the data source. It is simple string which normally contains source server, source database, source file path and authentication information. Connection string can be utilized in various configuration files and connection managers. Below is the sample example of connection string that connects to SQL Server database with integrated authentication.
Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=BI_Reporting; Data Source=IND-SQL-01\SQL2012
2. Steps to create connection string using Notepad:
i. Open Notepad and save the blank file in .UDL extension.
ii. After saving the file in .UDL file format, File icon will looks like below one:
iii. Double cilck on the file or write click and select open with OLE DB Core services. It will open the wizard for creating the connection string.
iv. Select the provider for your connection. For this example, we are using “Microsoft OLE DB provider for SQL Server”
v. Go to Connection Tab and give the name of the source server, database name and security credential details:
vi. Click on Test Connection to verify whether connection is establishing or not:
vii. Once Test connections succeeded, and then click ok. Now open the .UDL file with notepad and see the connection string:
viii. Below is the created connection string:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BI_Reporting;Data Source=IND-SQL-01\SQL2012
By using above steps, we can create connection string.