1

I installed SQL Server 2008 Express on Win 7 64. I can connect to it via SQL Server Management Studio using Windows Auth, but not SQl Server Auth. Following the directions in http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/, I feel I have properly configured for remote connections & sql server auth. Yet, when I try to login, I get an error stating that the login failed. Googling hasn't seemed to help for the answers do not make sense, do not seem to apply. One of these was a suggestion to change the dynamic port to blank, hard code the port. Since this tool is by default not installed in such fashion, I have a hard time believing this is the actual problem. Not one post I found explained why that should be changed.

The exact message is:

Cannot connect to bvl-wd-bturner\SQLEXPRess Additional information: Login failed for user 'DTN\bill.turner'. (Microsoft SQL Server, Error 18456)

I attempted to login with the following credentials:

Server type: Database Engine Server name: bvl-wd-bturner\SQLEXPRESS Authentication: SQL Server Authentication Login: DTN\bill.turner Password:

For what its worth, logging on using Windows auth, I look at the connection properties. The User Name complies with the login above. The Server Name is the same (in fact copy/pasted to be sure for both).

=== UPDATE === Windows Firewall is off.

I tried also, even with my reservations, to hardcode the port number, removing the "0" on all dynamic ports and setting the port to 1433. No luck. :-(

=== UPDATE 2 === I realized I needed to create a SQL Server account and have done so. I can now log in via SSMS with the following credentials. However, I seem to be unable to login through DbVisualizer or a test java file with the follow:

Driver is net.sourceforge.jtds.jdbc.Driver Connection url is jdbc:jtds:sqlserver://bvl-wd-bturner:1433/host

The credentials I now use successfully in SSMS are: Server type: Database Engine Server name: bvl-wd-bturner\SQLEXPRESS Authentication: SQL Server Authentication Login: local Password:

At this point I have to believe it is something obvious I am missing, but have yet to find it.

Bill Turner
  • 869
  • 1
  • 13
  • 27

3 Answers3

1

The login: DTN\bill.turner looks like a Windows login, not a SQL server login which has no domain component. Is DTN your domain name? If so, providing this with the SQL login will probably confuse the client into trying windows authentication, which will then fail if the login doesn't exist.

What is the name of the SQL server login that you have created (within the SQL management studio)? It shouldn't have a domain\username format, it should just be a username on its own. You also need to make sure that you select SQL authentication when connecting.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • I had not created a sql server account. Realized this after my last update. I have now created a user called "local" and can log SSMS using that. However, I have not found the key to making a connection in java, nor via DbVisualizer yet. I am using jtds driver with both. – Bill Turner Feb 11 '13 at 16:02
  • If you're using JTDS, see the following [question](http://stackoverflow.com/questions/1862283/help-me-create-a-jtds-connection-string) which explains the format required for the connection string. – steoleary Feb 11 '13 at 17:07
  • After reading that and other similar posts, it became obvious that I needed to specify the instance in the url string. I thought SQLEXPRESS was the default, thus not needed. So, my final connection string, for those stumbling upon this post, is "jdbc:jtds:sqlserver://localhost:1433/host;instance=SQLEXPRESS". – Bill Turner Feb 11 '13 at 23:04
0

Try adding your current logged in account (Windows) to the Local Administrators group on your computer.

Sunil
  • 51
  • 4
0

The link posted at the beginning, http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

Plus this article on setting the default log on database: http://blog.sqlauthority.com/2008/11/04/sql-server-fix-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user/

Solved this problem for me. Hopefully it will help others who wind up here.

SouthShoreAK
  • 4,176
  • 2
  • 26
  • 48