In this post I will be sharing the Step by step Procedure to Create a SQL Server database Connection with Common Error messages and steps to troubleshoot them.

I will be explaining the steps assuming you are working on your local system and not on client network. I will provide the steps for client connection in another post.

To Create a SQL Server Database Connection We need to have

1) SQL Server Installed in your local.

If you Don’t have SQL Server, Please download the Express Edition from the Microsoft Website. the link is below.

Microsoft SQL Server 2012 Express Edition

2) You are the administrator of the server.

3) You have a Database Created or Attached in the server (Ex: Adventure Works)

To download Adventure Works database please visit

Adventure Works DW 2012

Information on how to attach the DB in SQL Server please visit

AW DB in SQL Server

Once you have the SQL Server and the database

1) Launch Jaspersoft Studio and Click on the repository on the Top Left Corner.

2) Right Click on the Data Adapter and Click Create Data Adapter

Create DA

3) In the Data Adapter Wizard, select “Database JDBC Connection” and Click Next

Databse Wizard

4) Rename the Connection as “Adventure Works” and select the SQL Server 2005- 2012 Driver from the drop   down list as shown below.

Select

5) From the SQL Server Management studio copy the Database Name and Paste it in the database name section of the URL. Since the authentication is Windows Authentication we need to add the “integrated Security = true” line in the URL as shown Below

SQL Server Image URL

6) Click “Test” button and we will bump into our first error as below.

Error 1

7) To Resolve this error we need to provide access to the default user of the SQL Server Management Studio to read and write to the database.

8) Once you provide the access, click Test in Jaspersoft and Alas!! one more error. “net.sf.jasperreports.engine.JRRuntimeException: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver”

Class Not Found

9) This error means that the jdbc drivers are not present in the Jaspersoft Class path. We need to download the sql jdbc driver files. Please find he link below for the same.

Microsoft JDBC Driver 4.0 for SQL Server

10) This is a self Extract file and all the file will be extracted in C drive , Program Files folder. Check the Java SDK version you have and if its is more than 1.6 delete the sqljdbc.jar file and keep only sqljdbc4.jar file in the folder. Also make sure the sqljdbc_auth.dll file present in the path “Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth” matches with the Operating System bits. (If your OS is 64 bit, then keep the 64 bit Auth dll file)

11) Once this step is done. Click “Test” button again and the connection is successful. Please find the screenshot below

Connection Successful (www.rajeshsirsikar.com)

12) Press Ok and Then Click “Finish”, and this would create your new data adapter and is visible in the data Adapter Window.

New Data Adapter (www.rajeshsirsikar.com)