sql server remote connection
SQL server or SQLEXPRESS
installation for applications that use a common database on the network, and
the settings that need to be made to make the server available on the network
after installation.
Installation, server settings and
remote computer connections, which are a difficult and tedious process for the
user, must be done in a certain order.
Using databases on the network
requires some settings. Both programmers and users want the application to be
easy and trouble-free to install and not require many complicated operations
before or during use.
Databases that do not require
installation and adjustment are preferred in applications that will be used on
a single computer and do not need a large database. However, if the application
will be used on a network, it needs to set up a server. This brings with it the
obligation to set up the server and make its settings.
Installation, server settings and
remote computer connections, which are a difficult and tedious process for the
user, must be done in a certain order.
MS SQL Server Installation
If less than 10 GB of data will
be stored on the server, SQLEXPRESS, the free version of MS Sqlserver, can be
installed. SQL Express can be downloaded and installed from Microsoft's official site SQLEXPRESS.
At the last stage of the installation, the "ssms
install" button is displayed. It is necessary to click this button and
install Microsoft Sql Server Management.
With this application, the database can be viewed and processed.
MS SQL Server Settings
1.
Open Microsoft SQL Server Management Studio.
2.
In the Server Name field, type the server name
(.\SQLEXPRESS). Login without password with Windows Authentication option.
3.
Server will be displayed on the left of the
screen. Right click on it and choose Properties
from the popup menu.
4.
Select Security
from the page list on the left of the window that opens.
5.
Check SQL
server and Windows Authentication
6.
Select Connections
from the list of pages on the left.
7.
Check Allow
remote connections to this server. Exit with the OK button.
8.
Under Server, click the + sign next to Sequrity from the list to expand it.
9.
Right click on Login under Security. Select New Login... from the drop-down menu.
The login form will open
10.
On the right of the Login form that opens,
select the General option from the page list. Type a username in the Login Name field. Check SQL Server Authentication. Enter the
same password in the Password and Confirme Password fields.
11.
Uncheck
Enforce Password Policy (if checked, password change will be requested
periodically.)
12.
To determine the privileges to be granted, open
Server Roles from the page list on the left and select sysadmin from the server
roles list.
13.
Select the Status
page in the list on the left and set the Login setting to Enabled.
14.
Click OK.
Opening SQL Server to remote connections
1.
Open Sql
Server Configuration Manager.
2.
Expand SQL
Server Network Configuration.
3.
Select Protocols for SQLEXPRESS (or MSSQLSERVER).
4.
Right click on TCP/IP and select enable.
5.
Double-click TCP/IP.
6.
Click the IP
Addresses tab.
7.
The IP addresses of your server will be
displayed. Enable Yes from the
properties of the IP address you want to connect to.
8.
If the TCP Port is not empty, find out its
number. If blank, enter the number 1433.
9.
Exit with the OK button.
10.
Go to SQL
Server Services on the left.
11.
Right
click on SQL Server(SQLEXPRESS) in the middle and select Refresh from the menu. The service will
shut down and reopen.
Firewall Settings
1.
Turn on Windows
Firewall and Network protection.
2.
Click on advanced
settings.
3.
Click Inbound
Rules on the left of the page that opens.
4.
Click New
Rule in the list on the left.
5.
Click Protocol
and ports on the left.
6.
Choose TCP.
7.
In the Port
number field, type 1433.
8.
Click Next
9.
Select Allow
Connection. Click next
10.
On the page that opens, make sure all three options are selected. Click next
11.
Name the
rule and end with an ending.