Tuesday, July 22, 2008

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:

Labels: