SQL Server 2008 R2 Setup, Configurations & Remote Accessing using Java

Table of Contents

• Introduction
• System Requirements
• Configuration Steps
• History

Introduction

In this article, the configuration steps for remotely accessing SQL Server 2008 R2 using Java are described.

System Requirements

• Operating System: Windows 7 (preferable) / Windows Server 2008 standard
• Database System: SQL Server 2008 R2 (preferable) / SQL Server 2008
• .NET Framework: Microsoft .NET Framework 3.5 SP1
• Database Management Tool: SQL Server Management Studio

Configuration Steps

SQL Server 2008 R2 Installation

1. In Instance Name page, please select the Default instance
2. In Authentication Mode page, please select the Windows Authentication Mode

SQL Server 2008 to allow remote connections

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. Click SQL Server Services, make confirm SQL Server (SQLEXPRESS) and SQL Server Browser running. 
3. If SQL Server Browser is stopped, then select its properties and point to Service tab, change the Start Mode Disabled to Automatic, click the apply button, then click start option using right mouse click over SQL Server Browser.
4. Restart the SQL Server(SQLEXPRESS) using right mouse click over SQL Server(SQLEXPRESS).
5. Click SQL Server Network Configuration, point to Protocols for SQLEXPRESS, point to TCP/IP, make sure TCP/IP status is Enabled.
6. Open TCP/IP Properties form using right mouse click over TCP/IP, point to IP Address tab, point to TCP Port in Last section, change TCP Port to 1433, and click Apply button.

Create exceptions in Windows Firewall:

1. Click Start, point to Control Panel, point to Windows Firewall Settings
2. Click Change settings link, point to Exceptions tab
3. Click Add port... button, do the following:

   Name: 1433
   Port number: 1433
   Protocol: TCP

Click OK, and click apply.

Alternative process to create exceptions in Windows Firewall:

1. Click Start, point to Administrative Tools, open Windows Firewall with Advanced Security.
2. Click Inbound Rules, Click New Rule link at the top of right section.
3. Select Port radio button, click next.
4. Select TCP radio button, Enter port number in Specific local ports section such as:

   Specific local ports: 1433

5. Click next
6. Select Allow the connection, click next button, again click next button
7. Enter Name Ex. 1433
8. Click Finish button

Import database using .bak extension file:

1. In SQL Server Management Studio, Right-click the Databases folder, Click the Restore Database.
2. Enter database name into To database section. Example: testDB
3. Select the From device, and point the testDB.bak file location
4. Click the OK button

To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio):

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter a name for the new login in the Login name box. Example: test
4. Select SQL Server Authentication. 
5. Enter a password for the login. password example: test-1
6. Select the Enforce password policy and Enforece password expiration options. In general, enforcing password policy is the more secure option.
7. Confirm User must change password at next login is not selected.
8. Select Default database as testDB.
9. Click OK.

Add user in testDB database:

1. In SQL Server Management Studio, open Databases folder, expand testDB
2. Right-click the Security folder, point to New, and then click User
3. Enter a User Name, enter login name which has been created. Example: test
4. Select the db_datareader, db_datawriter (as you need) in Owned Schemas section
5. Click OK

Access database using JAVA:

1. Requirement: sqljdbc4.jar
2. Add jar file in Java project
3. Connection string
   Java

   String conString =jdbc:sqlserver://000.000.0.00\\SQLEXPRESS:1433;
   databaseName=testDB;userName=test; passWord=test-2;";

History

• 08 Jan, 2011: Initial version