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