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:
Igen ám, de vajon melyik IP címeken figyel a szolgáltatás? Ha a TCP/IP-n megnézzük (jobb gomb, properties), akkor alapból a Listen All = Yes.
Ebben az esetben, minden, még az APIPA és a Local-Link Address is használatban van. Ez azt jelenti, hogy bármely címen tud fogadni kéréseket az SQL példányunk. Ezt pedig a beépített TCP endpoint-on keresztül teszi.
Látható mind az SQL Server Configuration Manager-ben és az SSMS-ben is, hogy vannak itt ás protokollok is. Én most csak a TCP/IP-re fókuszálok. A TCP endpoint-ok egyébként lekérdezhetők az alábbi lekérdezéssel:
select * from sys.tcp_endpoints
Az eredménye (az én demó gépemen):
Az összes endpoint pedig ezzel kérdezhető le:
select * from sys.endpoints
Itt az eredmény:
Ezek részletezése is megérne pár szót, de most menjünk vissza inkább arra az esetre, amikor is IP címet változtatok és/vagy új hálózati kártya vagy IP cím kerül megadásra. Ugye alapvetően az összes címen figyel a szerver, ami látszik is a SQL Server Configuration Manager alkalmazásban:
Látszik, hogy az IpAll fix porton, a 1433-as TCP porton figyel. Itt fontos megemlítenem, hogy dinamikus portot nem állítunk sose, így a TCP Dynamic Ports üresen marad. ez ugye azért is érdekes, mert több SQL példány esetén akkor mi legyen? Természetesen adunk neki egy másik portot! Most ne menjünk abba bele, hogy mi van akkor, ha külön IP címeken de ugyan azt a portot akarjuk adni. Ilyen esetekben a névfeloldással (is) lehet "némi" probléma, arról nem is beszélve, hogy mennyire megnehezíti az üzemeltetést és a nagy rendelkezésre állási megoldások konfigurációját.
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:
- Letiltja a Listen All beállítást, azaz csak egy címen fog figyelni a szolgáltatás
- 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
- 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:
$InstanceName = "MSSQLSERVER"
$Version = "13" #SQL2016 13, SQL2017 14
$InstanceIP = "10.10.10.12"
#set ListenOnAllIPs 0, Enabled 1
Write-Host "Setting up ListenOnAllIPs 0, Enabled 1"
$regTcpRoot = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
Set-ItemProperty -Path $regTcpRoot -Name "ListenOnAllIPs" -Value 0
Set-ItemProperty -Path $regTcpRoot -Name "Enabled" -Value 1
# get tcp information from registry
$regTcpIPs = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL' + $Version + '.'+ $InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\*'
$objReg = Get-ItemProperty $regTcpIPs
#set tcp properties
$objReg | ForEach-Object {
if ($_.IpAddress -eq $InstanceIP)
{
Write-Host "Setting up " $_.IpAddress
Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 1
Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
}
else
{
Write-Host "Setting up " $_.IpAddress
Set-ItemProperty -Path $_.PSPath -Name "Enabled" -Value 0
Set-ItemProperty -Path $_.PSPath -Name "Active" -Value 1
Set-ItemProperty -Path $_.PSPath -Name "TcpPort" -Value 1433
Set-ItemProperty -Path $_.PSPath -Name "TcpDynamicPorts" -Value ""
}
}
#add missing IP for virtual host name ($InstanceIP) if not exist
if (($objReg | Where-Object { $_.IpAddress -eq $InstanceIP }) -eq $null)
{
Write-Host "Setting up new ip: " $InstanceIP
$regNew = $objReg.PSParentPath.GetValue(0) + '\IP' + $objReg.Count
New-Item -Path $objReg.PSParentPath.GetValue(0) -Name ('\IP' + $objReg.Count) | Out-Null
Set-ItemProperty -Path $regNew -Name "Enabled" -Value 1
Set-ItemProperty -Path $regNew -Name "DisplayName" -Value "Specific IP Address"
Set-ItemProperty -Path $regNew -Name "Active" -Value 1
Set-ItemProperty -Path $regNew -Name "IpAddress" -Value $InstanceIP
Set-ItemProperty -Path $regNew -Name "TcpPort" -Value "1433"
Set-ItemProperty -Path $regNew -Name "TcpDynamicPorts" -Value ""
}
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.