We help IT Professionals succeed at work.

Problems Connecting To SQL Server 2008

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
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
did you "enable" the sa account?
did you restart the sql server instance after changing the setting of the sql instance to allow both authentication?

Author

Commented:
Yes; and I noticed the weirdest thing when I was checking on the "IP Addresses" tab of TCP/IP properties: There were two Yes/No selectors for each IP "Slot": 'Active' and 'Enabled'.  So I first activated each of the entries that matched my needs, but I still couldn't telnet into the machine at port 1433.  So I went back and enabled each of those entries, restarted the SQL Server instance, and could connect via telnet.  So I thought my problems were over.  But they weren't.

I also noted that you can either have TCP Dynamic Ports, or a TCP Port, but not both.  So - to keep this simple - I opted for TCP Port 1433.  I can always get fancy with it later.

"Enable" the sa account?  You mean that radio button under the "Status" page for the sa account's login?  Yeah, after I changed the standard 'sa' password I enabled the acount - I thought it pretty smooth that it was disabled by default.  I don't need to restart the service after re-enabling a user account, do I?  I mean, I know that 'sa' is a special account, but that special?

Author

Commented:
Unfortunately, I still get that error "Test connection failed because of an error in initializing provider.  Login failed for user 'sa'".  I'm using the 'sa' account because typically it can connect to any database on the SQL Server, so the error wouldn't be one of permissions.  Only it seems like an error of permissions, because it cannot connect.

Frustrating,  I'm still curious as to what I may have missed when setting things up.  Mind you, it runs our SharePoint data for a treat; SharePoint connects via Windows Authentication, so naturally that would work.  And that's what's frustrating about this situation...

Author

Commented:
Okay, does anybody know what sorts of test(s) that I might employ to determine what might be wrong?  I still figure it's something we did wrong here, I just don't know exactly what to look for.  I do know that there is no firewall on the server itself (possibly dangerous but eliminated while we work out the actual issues with SQL Server FIRST) and there is no hardware firewall between the SQL Server and the rest of our network.

Someone once suggested seeing if I could Telnet into the 1433 port, and while I believe I have successfully connected to it, I cannot be sure as I have no idea what that connection should "say" once I get connected.  Is there some passphrase I need to send it (sort of like 'HELO' and 'EHLO' to start a POP3 server's connection)?

Thanks again - I'm really trying to make this thing work.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I have no idea on what goes wrong on your side :(

Author

Commented:
Me neither.

Question: Do you know what I'm supposed to "see" when I TelNet to the Server/Port:1433?  Is it supposed to try and handshake my terminal, put forward a prompt, or something?  Because I no longer get the "cannot connect" message, and I get a comms screen, but I'll be blasted if I know what I'm seeing, or supposed to see.

Thanks again!

Author

Commented:
Problem is resolved.  And I wouldn't have believed the resolution if I hadn't just fixed it just now.  This is one of those "trust but confirm" moments - and I'm extremely embarrassed to admit what just happened here.  But in the interests of those who will come after, I'm morally bound to reveal the solution, especially since angelIII spent time and effort trying to help me get this thing working.

"You can observe a lot by watching" - Yogi Berra

Deciding that the problem may not be related to connectivity issues, a friend suggested that we try connecting to the SQL Server locally as 'sa', and see what happened.  No good, the login failed there, too.  Which meant that it wasn't a problem connecting to the box, but a problem within SQL Server itself.  Before reaching for the telephone to call Microsoft Tech Support, I took a notion to re-visit the Security Settings for that SQL Server - you know, to make sure everything was ship-shape and in place for the interrogation that surely would follow during the Tech Support call.

And that is where I found it.  A SINO (SysAdmin In Name Only) had just recently "secured" the box for me a little earlier, and in so doing had turned off Mixed-Mode Authentication.  You know, to keep people (and scripts) from executing exploits against the SQL Server Authentication.  A quick change of the SQL Server box password will assure that that won't happen again.  But that is just soooooo embarrassing to admit.  Almost funny in a way, but in my current state of mind I am incapable of finding the humor in it.

So, angelIII, you get the points, because your first suggestion was spot-on, regardless of the fact that Mixed-Mode Authentication was how the machine was originally set up.  Guess I should pay more attention to these details, huh?

Thanks for your time and vigilant efforts! We're back up and functional, and now my external applications can make connections to the SQL Server without having to set up bogus Windows accounts (also a possible security issue) to make them work.

Thank you!  Thank you!  Thank you!

Author

Commented:
Changed the Security Setting for the SQL Server to all Mixed Authentication; re-started the instance, and everything works perfectly.

http://msdn.microsoft.com/en-us/library/ms188670.aspx for details on how to accomplish this feat, in case anyone was curious.