Sql server 2008 R2 setup, configurations & remote accessing using java
1. Introduction
2. System requirements
3. Configurations steps
Introduction
I am going to share my experience about Sql server 2008 R2 setup, configurations & remote accessing using java.
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:
Configurations 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 it’s 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. and 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. Ex. 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. Ex. 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 expirationoptions. In general, enforcing password policy is the more secure option.
7. Make 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
String conString =jdbc:sqlserver://000.000.0.00\\SQLEXPRESS:1433;databaseName=testDB;userName=test; passWord=test-2;";
Please feel free to ask me any question. I will try my beyond.
Regards, palash
