SQL Server

Server-side configuration for connecting to SQL Server with SQL Server authentication

Page creation date :

environment

SQL Server
  • SQL Server 2019
Windows (used as server)
  • Windows 10 Pro 20H2
Windows Server (used as a server)
  • Windows Server 2019

precondition

I have tried several versions of the OS and SQL Server to be installed, but in each case, the settings at the time of installing and after installing SQL Server are as follows.

  • SQL Server is installed with Administrators privileges.
  • The SQL Server installation path remains the default value.
  • The OS is almost immediately installed.
  • Assume that SQL Server Management Studio is installed on the server side.
  • Everything in these tips is server-side except for client connection checking.

The SQL Server feature selects only Database Engine Service.

Check for instances in both Default Instance and Named Instance.

Leave the service account at the default.

The authentication mode is Windows authentication only. Enable SQL Server authentication in a later configuration as a configuration step.

Create a database called "TestDatabase". I only created a new one for confirmation, and I didn't create anything inside.

Suppose you create an account called "TestUser" and perform SQL Server authentication against this account. You have access to "TestDatabase".

Enable SQL Server authentication

If you did not enable SQL Server authentication when you installed SQL Server, follow these steps:

Start Microsoft SQL Server Management Studio on the server side.

Log in to your server with Windows Authentication. If there is an instance name, enter it in the format "< server name >\< instance name>".

From Object Explorer, right-click the server and select Properties.

Select "Security" from the menu on the left and check "SQL Server Authentication Mode and Windows Authentication Mode" from "Server Authentication".

Once confirmed, you will be prompted to restart SQL Server.

Right-click the server from Object Explorer and select Restart. Reboot requires administrator privileges.

Select Yes to restart SQL Server.

Allow remote connections

Just in case you think it's enabled by default.

In SQL Server Management Studio, right-click the server from Object Explorer and choose Properties.

Select "Connect" from the menu on the left and make sure that "Allow remote connections to this server" is checked in "Remote server connection".

Login permission by sa account (only if necessary)

If you want to connect remotely with your sa account, set it up. SA is the highest level of privilege, so it's a good idea to disable it if you don't need it. By the way, if you have enabled SQL Server authentication during SQL Server installation, remote connections are already enabled for the sa account.

Open SQL Server Management Studio, expand Server, Security, and Login from Object Explorer, right-click sa, and select Properties.

Select "General" from the menu on the left and enter your password.

Select "Status" from the menu on the left and set "Login" to "Enable".

TCP/IP

Enabling TCP/IP

Open the Microsoft SQL Server 2019 folder from the Start menu and select SQL Server 2019 Configuration Manager.

* The name changes depending on the version of SQL Server.

From the tree on the left, expand SQL Server Configuration Manager (Local), SQL Server Network Configuration, and select MSSQLSERVER Protocol. Right-click TCP/IP from the list on the right and select Enable.

* The name of "MSSQLSERVER" will change to the name if the instance name is specified at the time of installation.

Click OK.

Select SQL Server Services from the tree on the left, right-click SQL Server (MSSQLSERVER) from the list on the right, and select Restart.

Checking or specifying the TCP port (if you are specifying an instance name)

If you do not specify an instance name, the default TCP port 1433 is specified, but if you specify an instance name, you must use other ports.

From the tree on the left, expand SQL Server Configuration Manager (Local), SQL Server Network Configuration, and select MSSQLSERVER Protocol. Double-click TCP/IP from the list on the right to open properties.

* The name of "MSSQLSERVER" will change to the name if the instance name is specified at the time of installation.

Click the IP Address tab.

Check the ports listed in TCP Dynamic Ports in the IPAll group. By the way, this value can be changed to any value. You specify this value later in the firewall settings.

firewall

Firewall (TCP)

From the Start menu, open the Windows Administrative Tools folder and select Windows Defender Firewall with Advanced Security.

Right-click Inbound Rules from the tree on the left and select New Rule.

Select Ports.

Select TCP.

If you did not specify an instance name for SQL Server, enter 1433 for Specific local port.

If you have specified an instance name for SQL Server, check in SQL Server Configuration Manager for "Specific local port", or specify the TCP dynamic port that you entered.

Make sure "Allow connection" is checked.

Please check only "Domain" and "Private". It is not intended to be published on the Internet.

Enter a name that will be easy to understand when you see it later. Here, it is "[Add] SQL Server TCP 1433".

The added state.

If you do not specify an instance name, this completes the configuration.

Firewall (UDP) (if you specify an instance name)

Open Windows Defender Firewall with Advanced Security, right-click Inbound Rules from the tree on the left, and select New Rule.

Select Ports.

Select UDP and enter 1434 for Specific local port.

Make sure "Allow connection" is checked.

Please check only "Domain" and "Private". It is not intended to be published on the Internet.

Enter a name that will be easy to understand when you see it later. Here, it is "[Added] SQL Server UDP 1434".

The added state.

Start SQL Server Browser (if you specify an instance name)

Open the Microsoft SQL Server 2019 folder from the Start menu and select SQL Server 2019 Configuration Manager.

* The name changes depending on the version of SQL Server.

From the tree on the left, expand SQL Server Configuration Manager (Local) and select SQL Server Services. Right-click SQL Server Rowzer from the list on the right and select Properties.

Select the "Services" tab and set "Start Mode" in the "General" group to "Automatic". Once selected, click the OK button.

When you return to the previous screen, right-click SQL Server Rowzer again and select Start.

This completes the configuration.

When you can't connect by all means

If SQL Server authentication does not lead even after making the settings so far, there is a possibility that you can connect by making the following settings. However, please note that it may be weaker than the previous setting in terms of security.

First, try connecting in SQL Server Management Studio

When you check the connection with a program or the like, there are many check items such as the connection user did not have access authority to the database, so let's first check whether SQL Server authentication can be performed from SQL Server Management Studio.

Is the network profile "private"?

Sometimes, you accidentally set it to "public" right after installing Windows or connecting to a new network. In this state, you will not be able to connect over the network in many other ways besides SQL Server. If you are in a secure network that is not an unspecified number of shared networks, set it to "private".

Allow sqlservr.exe on the firewall

Note that this setting is equivalent to specifying all ports to SQL Server.

Open Windows Defender Firewall with Advanced Security, right-click Inbound Rules from the tree on the left, and select New Rule.

Select Programs.

In This Program Path, specify the path of the following EXE: MSSQL15.SQLEXPRESS depends on the version of SQL Server.

%ProgramFiles%\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

Make sure "Allow connection" is checked.

Please check only "Domain" and "Private". It is not intended to be published on the Internet.

Enter a name that will be easy to understand when you see it later. Here, it is "[Add] SQL Server EXE".

The added state.

Steps to migrate all data between servers in SQL Server