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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
ASKER
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.
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.
I have no idea on what goes wrong on your side :(
ASKER
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!
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!
ASKER
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!
"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!
ASKER
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.
http://msdn.microsoft.com/en-us/library/ms188670.aspx for details on how to accomplish this feat, in case anyone was curious.
ASKER
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?