Introduction
Creating an SQL connection string for your DB can be fraught with problems; we don't do it every day, so it's difficult to remember exactly what needs to be there, and testing them can be a pain. This often affect beginners badly as it's not obvious what you need to type. But Windows to the rescue!
Background
Have you ever heard of UDL files? No? Nor had I until I found a use for them. UDL stands for Universal Data Link and the purpose of the file is to:
https://fileinfo.com/extension/udl says:
Universal Data Link (UDL) file used by Windows applications for specifying connection information to a data provider; defines the data provider type, the connection string, user name and password, and other properties, such as connection timeouts; can also be used for testing a connection to a data source.
So what can you do with them? Well, you can use them to generate a connections trign that will work in your C# or VB app, for starters.
Generating a Populated UDL File
Create an empty text file somewhere on your hard disk, called "myfile.udl". The easiest way to do this is to use Windows Explorer, right click a folder and select "New...Text Document" and then rename it to change the extension. You'll get a warning that changing an extension may make the file unusable, but since you created it, you're fine to press "OK".
Double click the file, and the "Data Link Properties" dialog will open:
Select your server, fill out the log on information, select the database. If you are using username and password (and I recommend it) tick the box marked "Allow saving password".
Test the connection with the button.
When it works, press OK - you may get a warning about the storage of insecure passwords which is fine, you want the password in the connection string:
You can also use the other tabs to select the advanced properties you need, if any.
Use any text editor (including Visual Studio, just drop the file on the edit window and it'll open) to examine the content:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=XXX;Persist Security Info=True;User ID=XXX;
Initial Catalog=SMWallet;Data Source=GRIFF-DESKTOP\SQLEXPRESS
Remove the bit at the front of the initstring: "Provider=..." up to and including the first semicolon (SQL Server doesn't support the Provider keyword)
Password=XXX;Persist Security Info=True;User ID=XXX;Initial Catalog=SMWallet;
Data Source=GRIFF-DESKTOP\SQLEXPRESS
You can copy and paste this string into your app (bad idea) or a configuration file (better idea).
Notes
This works in Windows 10 (even if SQL isn't installed on the PC), and should work all the way back to at least XP (please let me know if you test it on an OS not listed here as OK and I'll update the list), and certainly works for SQL Server but should work for other servers as well if you select the appropriate connector in the first tab - again, if you test it for a specific server, let me know.
History
- 2017-07-26 First version
- 2017-07-26 V1.1 2 pictures vanished from submitted article ... relaoded and re-inserted
