Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Tip/Trick

Simple SQL Connection String Creation

Rate me:
Please Sign up or sign in to vote.
4.38/5 (16 votes)
26 Jul 2017CPOL2 min read 23.1K   9   8
Did you know that Windows provides a built in way to build SQL connection strings for you? Neither did I ... until I found UDL files.

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:

Image 1

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".

Image 2

Test the connection with the button.

Image 3

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:

Image 4

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kornfeld Eliyahu Peter29-Jul-17 23:32
professionalKornfeld Eliyahu Peter29-Jul-17 23:32 
QuestionSQL String Builder Pin
milo-xml28-Jul-17 1:57
professionalmilo-xml28-Jul-17 1:57 
QuestionNot really new Pin
ObiWan_MCC27-Jul-17 21:33
ObiWan_MCC27-Jul-17 21:33 
GeneralMy vote of 5 Pin
Member 1236439027-Jul-17 21:03
Member 1236439027-Jul-17 21:03 
Question[My vote of 1] lol Pin
small_bob26-Jul-17 23:50
small_bob26-Jul-17 23:50 
General[My vote of 1] lol Pin
small_bob26-Jul-17 23:50
small_bob26-Jul-17 23:50 
Generallol Pin
small_bob26-Jul-17 23:50
small_bob26-Jul-17 23:50 
GeneralRe: lol PinPopular
Bob McGowan27-Jul-17 7:01
Bob McGowan27-Jul-17 7:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.