Tuesday, June 12, 2012

Connecting Netbeans 7.1 to MS SQL Server 2012

Usually I use Java DB/Derby database for my small projects but never used SQL Server in any of my past projects and found lots of difficulties and headaches in creating a JDBC connection. However after days of troubleshooting I finally could successfully connect to SQL Server. I will explain the steps in this blog.

Software used:
- Netbeans 7.1
- MS SQL Server 2012 Enterprise Edition
- Microsoft JDBC Driver

I assume you already have your own versions of netbeans and SQL Server, if not you can download them from their own website (you can get a developer version of SQL Server for free).

Microsoft JDBC Driver:
You can download this driver from official MSDN website. After you download, execute/unzip the file, copy the unzipped folder and paste it in the SQL Server installation folder so it will be easier to remember its path (or any other directory you want).

TCP Listening Port
Make sure your Server is listening on the correct port (Default port is 1433, but you can change that if needed). Follow these steps to make sure your server is listening on the right port:
- Open SQL Server Configuration Manager
- On the left column expand SQL Server Network Configuration
- Click on Protocols for <your server name>
- On the right column, right-click on TCP/IP and click properties
- In the protocol tab make sure that your TCP IP is enabled and listens to all
- In the IP Addresses navigate to IPAll section and make sure the Dynamic ports is set to 1433 (or the port you want), if not change it to that value.
- Click Ok and you will be prompted with a warning message indicating that the changes will not take effect untill you restart the service.
- Now in the left column click on SQL Server Services.
- Find your server on the right column (SQL Server (<your server name>).
- Right-click on the server and click Restart.

Make sure your TCP is listening to your port
 You need to make sure that the port you have defined for SQL Server is listening. To do that open the command prompt and write the following command: netstat -a
You can track all the active ports on this list. Try to find your port from this list

Create DB Server
On the installation of MS SQL Server 2012 it will prompt you to create a server. Make sure you choose the SQL Server Authentication (in opposed to Windows Authentication mode) and assign a username ("sa" by default) and a password. This is because netbeans cannot establish connection with SQL Server through JDBC if it is on Windows Authentication mode. However, if If you already have chosen your authentication mode to Windows Authentication you can change it by following the steps in the following section

Change Authentication Mode
- Open Microsoft SQL Server Management Studio.
- In the Object Explorer, Right-click your server and click Properties
- Select the security page
- Change the Server Authentication mode from Windows Authentication mode to SQL Server and Windows Authentication mode
- You will receive the warning message that changes will not take effect until you restart your server.
- Before you restart, you need to enable the login for the server.
- In the Object Explorer expand your server
- Expand the security folder.
- Expand logins folder
-  Right-click "sa" and click properties
- In the status page, set the login "enabled"
- In the general page, you might have to create and confirm a password for the login.

Configuring JDBC Driver with Netbeans:
- Open netbeans and open the services tab.
- Right-click the database and click new connection
- From the driver drop down menu, select "New Driver"
- Click Add to add a driver file, the driver file would be the JDBC driver you have downloaded from MSDN. Navigate to the folder you have pasted in SQL installation folder (or any directory you have pasted in), choose "sqljdbc4.jar" and click open.
- The name and class name will be filled once you select the JDBC Driver file. The class name will be: "com.microsoft.sqlserver.jdbc.SQLServerDriver" click ok to make the new driver.
- Now with selected driver, click next
- Fill the following information:
       *Host: localhost
       *Port: 1433 (or the port you have selected previously)
       *Database: the name of the database you created
       *User Name: sa
       *Password: <the password you have defined for the database authentication>
- Click next and make sure you receive no errors. If you received no errors that means netbeans can successfully connect to the db. Click next and choose the schema. 
- Click finish and Bingo!!! You are successfully connected to MS SQL Server through netbeans.

This is how it worked for me!, you might find other errors that I did not face, I had to do lots of troubleshooting to find out some errors. But if you followed the instructions carefully, you will probably get no errors. Please leave a comment and good luck :)

36 comments:

  1. I was having issues with the authentication but this solved my problems. Thank you Fareed!

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. i no see tables in the databases, show this message...
    "the database conected no have tables..... " but i see databases in the services in the netbeans menu, whats whrong ?

    ReplyDelete
    Replies
    1. It means you are connected to the database but the database has no tables. Or I think you are trying to access a different schema. Try dbo

      Delete
  5. Still cannot connect,
    "...The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect..."
    I've tried to do the same as the topic said but why can't :(
    Even in the command prompt, I haven't seen the 1433 port.
    Im using windows 8, SQL Server 2012 and NetBean 7.1.3
    ANY HELP ???

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. Thank you so much!!! I searched during hours and tried everything but still I couldn't connect...
    But I found this information you posted, followed each step, and it worked the first time I tried!
    Thanks a lot!!! I needed to work in my project and I couldn't do it without connecting first.

    ReplyDelete
    Replies
    1. I'm so glad you were able to connect, good luck with your project :)

      Delete
  8. thanks
    but in local pc when i use IP then show connection failed.

    ReplyDelete
  9. am getting the following error when i build my code :

    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

    ReplyDelete
    Replies
    1. That means you the driver was not found. Have you downloaded the file from msdn? if you did make sure you are connecting through the right driver in Netbeans

      Delete
  10. thanks a lot, me and my team were having some problems with the ports but this helped us, very useful

    ReplyDelete
  11. Thanks BROTHER!!!!
    It works for me....
    ThankYou!!

    ReplyDelete
  12. My Port setting was wrong!
    Thanks.

    ReplyDelete
  13. It worked thanks a lot, I really appreaciated it

    ReplyDelete
  14. Thanks a lot bro. 2years later your post is still relevant.

    ReplyDelete
  15. till step listening port when i click on sql server services it showing on write side the remote procedure call fail.... any help?

    ReplyDelete
  16. its also not showing in listening port list when i use this command netstat -a

    ReplyDelete
  17. Thanks this really helpful.I have an example with tables it is working on MySql server.I want to open it on sql server but I couldn't see my table.Do you have any example project or code about accesing to table ? İf you have ...Can you share on your blog or sen me mail.

    ReplyDelete