We help IT Professionals succeed at work.
Get Started

Problems Connecting To SQL Server 2008

Last Modified: 2012-05-12
Hi There!

I've got a SQL Server 2008 installation living "out there" on my network, and it responds to Windows Authentication for a treat.  But when I try to use SQL Server Name and Password authentication it fails every time.  Every time.  The message is always the same "Test connection failed because of an error in initializing provider.  Login failed for user 'sa'" - or whatever.

Steps so far taken:  I have ensured that port 1433 is being "listened to", and if you telnet to the box at port 1433 it actually connects, which I think is a good sign.  (It used to error out with no connection, so at least it's progress.)  I have made sure the local OLE-DB providers work by connecting to a local (on my hard drive) Excel file, to which my machine connects perfectly.

About the test environment: I am using (abusing) a UDL file prepared thusly: on the desktop, right click and select "New", then select "Text Document".  When given the opportunity to name/rename this new Text Document, change the name to "X.UDL" and save it.  If you've got "Hide extensions for known file types" turned off, Windows will post a warning about changing the extension.  Which you will be fine with - so click OK and move on.  Double-click on the new "X.UDL" file, and Windows will magically open it in the "Data Link Properties" window.  On the "Provider" tab select "Microsoft OLE DB Provider for SQL Server".  Click the "Next" button.  On thye "Connection" tab select the SQL Server from the drop-down listbox next to Step 1, and select "Use a specific user name and password" at Step 2, and provide same.  Even though it's a severe security risk, check the check-box next to "Allow saving password", and then at Step 3 click the drop-down listbox to select the database on the server.  ERROR pops up - "Microsoft Data Link Error": "Login failed for 'sa'." followed by "Login failed.  Catalog information cannot be retrieved."

I think I've made sure that the SQL Server supports both Windows Authentication and SQL Server Authentication, but for some reason I cannot connect using a Specific username and password.  What did I miss?

Thanks for your time and attention,

- The Lurking LongFist
Watch Question
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE