Something I always forget is when you just want to test a connection or create a connection string, there is this awesome little trick which is part of Microsoft Data Access Components (MDAC) on Windows 2000 and above. The Universal Data Link or UDL (.udl) File. To create the file is as easy as right clicking on the desktop or in a Windows Explorer window and clicking on create New text Document. Right Click on the file again, rename the extension to .udl. Ignore the warning that is shown ” A warning that changing file extensions can cause files to become unusable might appear.”
Now we can configure the .udl file
-Double-click the .udl file. a Properties dialogue box opens, displaying the following tabs: Provider, Connection, Advanced, and All.
-On the Provider tab, select a database provider.
-On the Connection tab, either select the data source name (DSN) of an available Provider, or enter a custom connection string. Valid DSNs for providers that are pre-defined on your system -are displayed in the Use Data Source drop-down list.
-Use the Advanced tab to view and set other initialization properties for your data.
-Use the All tab to review and edit all OLE DB initialization properties available for your OLE DB provider.
-Choose OK to save the connection string to the.udl file.
– On the connections Tab you can even test the connection
Okay, so now we can open the file in NotePad or NotePad++ and we will see our connection string.
[oledb] ; Everything after this line is an OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=######;Initial Catalog=######;Data Source=######