SQL SERVER database and user

1) First create the user in the sercurity/Logins by right clicking in the right window.

2) Enter the login name and the password. If you are allowing the external users to connect then select the sql server authentication. Also select the database you allow the user to access.

3) Select the appropriate roles for the user

4) Finally in the Database Access page select the databases you allow the user to access. Click Ok and save the user and the credentials. Do not confuse with the one selected in the section 2. That is the default database, the ones you select now are the databases the user is permited.

5) Now open the database you want the user just created to access. Select the user and in the right window right click and select New Database User.

6) Enter the user name and the password and you are all set. The above can be done by selecting the in the user name.

Now when you try to write a java application to connect to this database, you may see the error/exception as

Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(Unknown Source)at com.microsoft.jdbc.sqlserver.SQLServerImplConnection.open(Unknown Source)at com.microsoft.jdbc.base.BaseConnection.getNewImplConnection(Unknown Source)at com.microsoft.jdbc.base.BaseConnection.open(Unknown Source)at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)at java.sql.DriverManager.getConnection(DriverManager.java:512)at java.sql.DriverManager.getConnection(DriverManager.java:193)

This can be solved by following the steps below.

First try the telnet 1433, if this fails then the server configuration has to be changed.

1. Make sure that SQL Server is set to mixed authentication. To do this, open enterprise manager, right click on server registered on there, go to security tab and select SQL Server and Windows Authentication.

2. Go to the general tab and click the network configuration button. Enable TCP/IP. Click properties to change the default port (1433).

3. Add an SQL Server login account (NOT a WINDOWS account (see 1)). You can also use your sa account.

Happy SQLing..