Basics – Test DB connection with UDL

Basics – Test DB connection with UDL
3 votes, 5.00 avg. rating (97% score)

Introduction

Recently my SharePoint application is giving error ‘cannot connect to configuration database’. When nothing wrong with the services, do the first basic test. Check if the server is able to connect to DB. If it is succeded then it might be service issue or debug further through ULS logs. Here is the very basics of creating UDL file and check DB connection using the OLE DB providers installed on the server.

Creating UDL file

1) Create a new empty text file like “test.txt” (For example, right mouse click on an empty place on your desktop, select “New” and “Text Document”)


2) Rename the file as “test.udl”

  As soon as you renamed the file, text file icon should change to a UDL icon

3) Double click on test.udl file and you’ll receive a window titled “Data Link Properties

You’ll get all the installed OLE DB Providers on the box when you’ve switched to the Provider tab. If the OLE DB Provider for the database/datasource you’re interested in is in the list, select the OLE DB Provider and click on “Next“and you’ll switch to “Connection” tab and will be ready to play with the OLE DB Provider. The rest depends on the OLE DB Provider you selected.

For example if you selected “Microsoft OLE DB Provider for SQL” , you’ll  see a window like that :

You’ll need the name of the SQL Server (or instance name) you’re trying to connect to the textbox in the 1st part. “Use a specific user name and password” is selected by default in the 2nd part and this means “SQL Authentication”. You’ll need to enter a SQL Server username here (P.S. : It’s a common mistake to type a Windows/Domain user like “mydomain\myuser. This WON’T work! You can only type a SQL Server Username like “sa” etc. which is called as SQL Server user). If you select “Use Windows NT Integrated security” radio button, then the Username and Password textboxes will be disabled and the credential that you logged on to the machine will be used while connecting to SQL Server. Be sure that your SQL Server is in “mixed” mode (Please refer to SQL Server Books Online for the details )

You can either click on “Test Connection” directly to test the connection or click on the combobox to enumerate the available databases/catalogs on the server.

If you click on “Test Connection“, you should receive “Test connection succeeded” message if you can connect to the SQL Server :

If you click on “OK” and click “OK” again, the information that you entered will be saved into the test.udl file. The UDL file is a simple text file and the magic line is the last line which is the “Connection string” Here’s an axample for a SQL Server OLE DB Provider scenario when “Use Windows NT Integrated security” selected :

=============TEST.UDL file BEGIN=========

[oledb]

; Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME

=============TEST.UDL file END===========

Having the connection string from a UDL file

  1) Will prevent you to make typos in the connection string (You’re going to be sure that the connection string is right and working, cause you’ve got “Test connection succeeded” рЯШЙ )

  2) Will help you to “generate” the connection strings which are using different types of OLE DB Provider that you’re not familiar with their syntax. The parameter/value pairs are specific to the OLE DB Provider    manufacturer. Of course, all OLE DB connection strings should have Provider=SOMEABBREVIATONFORTHEOLEDBPROVIDER pair (I can assure you that you’ll never see that long “abbreviation” for an OLE DB Provider :o)  )

cross post from http://blogs.msdn.com/b/farukcelik/archive/2007/12/31/basics-first-udl-test.aspx
Author : Faruk Celik, Microsoft, MSFT

April 8, 2015 ¬Ј Adi ¬Ј No Comments
Tags: ,  ¬Ј Posted in: Basics, Sharepoint 2010, SharePoint 2013, SQL

Leave a Reply

What is 6 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)