Connection to Microsoft SQL Server 2005 with JDBC

This has taken me a while to figure out, from reading various blogs, so heres a quick post to hopefully make the set up easy.

Connect with Integrated Authentication:

1. Download and Install MS SQL Server 2005 express
   
2. During install check the box to enable integrated authentication
3. Run 'SQL Server Surface Area Configuration'
4. Click 'Surface Area Configuration for Services and Connections'
5. In the left hand pane click 'Remote Connections'
6. Click 'Local and Remote Connections' Radio Button and then 'Using TCP/IP only'
7. Right click 'My Computer'->Manage
8. Click 'Services and Applications'->'Services'
9. Right Click on 'SQL Server Browser'->Properties
10. In the Startup Type drop down select 'Automatic' then click the 'Apply' then click 'Start'
11. Install and run 'Microsoft SQL Server Manager Studio Express'
12. Note the server name for example "MY-PC\SQLEXPRESS"
13. Download the JDBC driver from Microsoft
    
14. Unzip the driver
15. In the unzip folder find the file 'sqljdbc_auth.dll' for your computer architecture and JVM, in the folder 'sqljdbc_\enu\auth\'
16. Place the file 'sqljdbc_auth.dll' on your windows system path (this is required for integrated authentication)
17. Open your favourite java code editor with the jdbc jar in the class path write the following code:

import java.sql.*;
public class test{
public static void main(String[] args) {
try{
  java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://MY-PC\\SQLEXPRESS;IntegratedSecurity=True");
  System.out.println("Connected!");
}catch(Exception ex){
  ex.printStackTrace();
}
}
}

Connect with SQL Server Authentication:

1. Download and Install MS SQL Server 2005 express
   
2. During install check the box 'SQL Server and Windows Authentication Mode'
3. Run 'SQL Server Surface Area Configuration'
4. Click 'Surface Area Configuration for Services and Connections'
5. In the left hand pane click 'Remote Connections'
6. Click 'Local and Remote Connections' Radio Button and then 'Using TCP/IP only'
7. Right click 'My Computer'->Manage
8. Click 'Services and Applications'->'Services'
9. Right Click on 'SQL Server Browser'->Properties
10. In the Startup Type drop down select 'Automatic' then click the 'Apply' then click 'Start'
11. Install and run 'Microsoft SQL Server Manager Studio Express'
12. Note the server name for example "MY-PC\SQLEXPRESS"
13. In the object Explorer Open Security->Logins folder
14. Right Click Logins folder-> New Login
15. Enter a Login name, check 'SQL server Authentication', enter a password
16. Select 'User Mapping' page, check a tick box next to the database name the user is to be connected to
17. In the database role membership check: db_datawriter, db_datareader, public
18. Click OK
    
19. Download the JDBC driver from Microsoft
    
20. Open your favourite java code editor with the jdbc jar in the class path write the following code:

import java.sql.*;
public class test{
public static void main(String[] args) {
try{
  java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://MY-PC\\SQLEXPRESS;user=MyUserName;password=MyPassword;");
  System.out.println("Connected!");
}catch(Exception ex){
  ex.printStackTrace();
}
}
}

And your done!

I found the following links really helpful:

• Angel Saenz-Badillos on MSDN
• http://jena.sourceforge.net/DB/mssql-howto.html
• MSDN building a connection URL