SQL Server hálózati beállítások

Talán az egyik legproblémásabb területe az SQL Server konfigurálás során a hálózati beállítás. Aki először találkozik a termékkel, de később is :), problémát szokott jelenteni, hogyan lehet engedélyezni az SQL Server database engine távoli elérést is. Anno, a 2005-ös verzió esetén már az volt a Microsoft mondása, hogy csak azok a szolgáltatások és protokollok vannak engedélyezve, amire valóban szükség van. Alapvetően a TCP kapcsolatok csak az Enterprise és a Developer verziók esetén vannak alapból engedélyezve, a többi verziónál le vannak tiltva. Ezt a telepítés során, amennyiben config file vagy parancssori telepítés van használatban, a TCPENABLED="1" beállítással lehet engedélyezni. Ilyenkor az összes, a telepítés során megtalált IP címen, hálózati kártyán figyelni fog valamilyen porton az adatbázis szolgáltatás.

Alapvetően a protokollok engedélyezése az SQL Server Configuration Manager segítségével történhet, az alábbi képen látható helyen, a default instance esetén:

1select * from sys.tcp_endpoints

Az eredménye (az én demó gépemen):

1select * from sys.endpoints

Itt az eredmény:

A másik, amit észre lehet venni a fenti képen, hogy az IP9 ( a 9. ip cím) Active = Yes, azaz létezik, ez mindig Yes, illetve Enabled = Yes, azaz ezen a címen biztos fogad kéréseket a szolgáltatás.

Akkor most "csavarok" egyet rajta: mi a helyzet, amikor csak egy adott IP címen akarom, hogy fogadjon kéréseket a szolgáltatás? Ilyen esetben annyi a dolgunk, hogy:

• Listen All = No
• az összes olyan IP címnél, ahol nem akarjuk, hogy a szolgáltatás fogadja a kéréseket legyen az Enabled = No,
• az egy vagy több IP címen, ahol akarjuk, hogy válaszoljon a szolgáltatás, ott az Enabled = Yes.

Ugye nem is nehéz :). Akkor most ismét "csavarok" egyet rajta: mi a helyzet, amikor egy új IP cím jön a képbe és csak azon szeretném, hogy figyeljen az adatbázis szolgáltatás? Hát igen, ez az az eset, ami már nem annyira triviális. Az SQL Server telepítője a telepítés idején beállításra került hálózati címeket olvassa fel csak, amennyiben változás van ezekben, már nem követi vissza, azaz nem töröl és nem vesz fel újabb címeket! Ilyen esetekben nincs más megoldás, mint a Registry kézi beállítása: a HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<verzió>.<instance név>\MSSQLServer\SuperSocketNetLib\Tcp\ kulcs szerkesztésével lehet ezt elérni. A verzió 13 SQL 2016, 14 SQL 2017 esetén pl. Az instance név, amit megadtunk a telepítés során, illetve default instance esetén az MSSQLSERVER.

Erre készítettem egy PowerShell scriptet, ami megcsinálja a szükséges lépéseket:

1. Letiltja a Listen All beállítást, azaz csak egy címen fog figyelni a szolgáltatás
2. Amennyiben fel van véve már az IP cím, akkor csak azt fogja engedélyezni Enabled = Yes, a többit letiltja, azaz Enabled = No
3. Amennyiben ez egy új IP cím, akkor felveszi és a 2. pontban megadott konfigurációt állítja be.

A scriptet mindenki saját felelősségére használja, érdemes kipróbálni egy teszt környezetben, mielőtt éles szerveren is használatban lenne. Íme a script:

 1$InstanceName = "MSSQLSERVER"
 2$Version = "13" #SQL2016 13, SQL2017 14
 3$InstanceIP = "10.10.10.12"
 4 
 5#set ListenOnAllIPs 0, Enabled 1
 6Write-Host "Setting up ListenOnAllIPs 0, Enabled 1"
 7$regTcpRoot = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
 8Set-ItemProperty -Path $regTcpRoot -Name "ListenOnAllIPs" -Value 0
 9Set-ItemProperty -Path $regTcpRoot -Name "Enabled" -Value 1
10 
11# get tcp information from registry
12$regTcpIPs = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\*'
13$objReg = Get-ItemProperty $regTcpIPs
14 
15#set tcp properties
16$objReg | ForEach-Object {
17                            if ($_.IpAddress -eq $InstanceIP)
18                            {
19                                Write-Host "Setting up " $_.IpAddress
20                                Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 1
21                                Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
22                                Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
23                                Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
24                            }
25                            else 
26                            {
27                                Write-Host "Setting up " $_.IpAddress
28                                Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 0
29                                Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
30                                Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
31                                Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
32                            }
33                             
34                          }
35 
36#add missing IP for virtual host name ($InstanceIP) if not exist
37if (($objReg | Where-Object { $_.IpAddress -eq $InstanceIP }) -eq $null)
38{
39    Write-Host "Setting up new ip: " $InstanceIP
40    $regNew = $objReg.PSParentPath.GetValue(0) + '\IP' + $objReg.Count
41    New-Item -Path $objReg.PSParentPath.GetValue(0) -Name ('\IP' + $objReg.Count) | Out-Null
42    Set-ItemProperty -Path $regNew -Name "Enabled" -Value 1
43    Set-ItemProperty -Path $regNew -Name "DisplayName" -Value "Specific IP Address"
44    Set-ItemProperty -Path $regNew -Name "Active" -Value 1
45    Set-ItemProperty -Path $regNew -Name "IpAddress" -Value $InstanceIP
46    Set-ItemProperty -Path $regNew -Name "TcpPort" -Value "1433"
47    Set-ItemProperty -Path $regNew -Name "TcpDynamicPorts" -Value ""
48}

A scriptben definiált változók jelentése:

• $InstanceName: ez az SQL példány neve, pl. a default instance esetén ez MSSQLSERVER
• $Version: SQL Server fő verzió száma:
  • SQL 2017: 14
  • SQL 2016: 13
  • SQL 2014: 12
• $InstanceIP: ez lesz az az IP cím, amin a szolgáltatás figyelni fog, és csak ezen.