RAID concepts and configuration in simple words for Windows Admin.

Continue reading

Advertisements

#raid, #sql, #storage, #windows

SID to AD objects conversion using SQL query.

Few years back I was asked by many customers to generate a report that shows assigned permission on resources. The glitch is, resources were mapped with SID of the domain objects (user /group) and to convert SID to AD object we had to create linked with AD database.

Finally we were able to create a temp function that can convert SID to AD object. I would not write an entire story but will share a use case that may be useful for many other DBA/ programmer.

-- Create a DB Name SidConversionDemo.
Use master
Create database SidConversionDemo
GO
-- Create table that holds resources & SID who they have permission. 
Use SidConversionDemo
Create Table Resources
    ( 
    ID INT IDENTITY(1,1), 
    SID VARCHAR(Max), 
    NAME VARCHAR (100)
    )
GO
-- Insert few rows. Please note, in production table schema might be different. Following SIDS are taken from my test SQL server and will not be the same for your environment.  
USE SidConversionDemo
INSERT INTO Resources VALUES
('S-1-5-21-3472616350-1934245764-4034525558-1104', 'Room1'),
('S-1-5-21-3472616350-1934245764-4034525558-1120', 'Room2'),
('S-1-5-21-3472616350-1934245764-4034525558-1172', 'Room3'),
('S-1-5-21-3472616350-1934245764-4034525558-1185', 'Room4'),
('S-1-5-21-3472616350-1934245764-4034525558-1186', 'Room5'),
('S-1-5-21-3472616350-1934245764-4034525558-1187', 'Room6'),
('S-1-5-21-3472616350-1934245764-4034525558-1188', 'Room7'),
('S-1-5-21-3472616350-1934245764-4034525558-1189', 'Room7'),
('S-1-5-21-3472616350-1934245764-4034525558-1191', 'Room8'),
('S-1-5-21-3472616350-1934245764-4034525558-1206', 'Room9')
GO

 -- Select current stored rows & column. 
USE SidConversionDemo
SELECT ID, SID, NAME FROM Resources
GO

–Expected output with SID only, difficult to find out AD objects (user/group..etc) associated with SID.

first

-- Now to change SID TO ADObject we need to create Function called 'dbo.sidconvert' as there is no inbuilt function.
USE SidConversionDemo
If OBJECT_ID (N'dbo.sidconvert', N'FN') IS NOT NULL
    DROP FUNCTION sidconvert;
GO
CREATE FUNCTION dbo.SidConvert (@MYSID AS VARCHAR(max))
RETURNS varbinary(256)
As
BEGIN
       If LEN (@MYSID)>12                    
BEGIN                    
DECLARE
@A AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT
SET @MYSID = REVERSE(@MYSID)                    
SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @B = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @A = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
RETURN 0x010500000000000515000000                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))                    
END
              BEGIN 
              RETURN null
              END
END
GO
-- Now run select query again and use dbo.sidconvert function. Following example also include well known global SID
USE SidConversionDemo 
SELECT 
    ID, 
    SID, 
    NAME, 
    CASE ISNULL(SID,'No') WHEN 'No' THEN 'No Permission' ELSE SID  END SID,
    CASE ISNULL (SID, 'No') WHEN 'NO' THEN 'No Permission'  WHEN 'S-1-3-0' THEN 'CREATORE_OWNER' When 'S-1-1-0' then 'EVERYONE'
    WHEN 'S-1-5-11' THEN 'AUTHENTICATED_USER' WHEN 'S-1-5-32-544' Then 'BUILT-IN ADMINISTRATORS' WHEN 'S-1-5-32-545' Then 'USERS'
    WHEN 'S-1-5-18' THEN 'LOCAL_SYSTEM' WHEN 'S-1-5-32-547' THEN 'POWER USERS'
    Else SUSER_Sname(dbo.sidconvert(SID))  END 'AD_Object'
FROM Resources
GO

–Expected output with SID and AD objects using Sidconvert function.  

second

-- Once testing done, you can drop database called SidConversionDemo
USE master
Drop Function sidconvert
DROP DATABASE SidConversionDemo 

I have discussed similar stuff in Microsoft & VERITAS forum.
Microsoft
Veritas

Please note:-
All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about SQL Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.
If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#active-directory, #sid, #sql

SQL server generate high NetLogon traffic due to missing / incorrect SPN.

I recently got an opportunity to work with one of the customer where AD team reported that they were observing high NetLogon requests on DC’s originating from SQL servers.

To make the concept simpler, SQL clients (OR front end application server) uses windows authentication either using NTLM or Kerberos through the Windows Security Support Provider Interface (SSPI) on TCP-IP/Named pipe protocol. By default, Kerberos authentication is always attempted first.

NTLM is legacy way of authentication in AD environment, Each NTLM-based authentication is unique—even if it’s a repeated authentication to the same resource by the same identity. NTLM is a more expensive authentication protocol in terms of network traffic, as well as a less secured one.

Kerberos, on the other hand, provides a reusable access-granting service ticket for the resource to that identity, and that reuse requires no interaction with an authenticating server or domain controller (DC).

Kerberos authentication require certain information from the SQL client (application OR front end server) that needs to be match with information saved in AD database for services. This unique information about the combination of service name, service account, server name & service port, is known as SPN (Service principal name). SQL client send authentication request with following information.

  1. Type of service (In this case SQL Server MSSQLSvc).
  2. The Name of the server.
  3. The Port.
  4. The service account running the service.

If above information matches with SPN stored in AD then auth scheme will use Kerberos otherwise it will fail over to NTLM (less desirable auth scheme).

 


Saved SPN on AD database can be seen with following two option.

  • Open command prompt in any domain joined machine with elevated command prompt and run SetSPN with following parameter.
SETSPN –L  OR 

If SQL service running under domain account then it uses login name.

SETSPN –L myDomain\SQLsvc

Expected output:-

Registered ServicePrincipalNames for CN=sqlsvc,CN=Users,DC=myDomain,DC=local:
  MSSQLSvc/SQL1A.myDomain.local:1433
  MSSQLSvc/SQL1A.myDomain.local

If SQL service running under Local System/Network Service then SQL server name needs to used.

SETSPN -l myDomain\sql1a

Expected output:-

Registered ServicePrincipalNames for CN=SQL1A,CN=localputers,DC=myDomain,DC=local:
  MSSQLSvc/SQL1A.myDomain.local:1433
  MSSQLSvc/SQL1A.myDomain.local
  • Log in to the server running your Active Directory service and open Adsiedit.msc:

Expand Domain [YourDomainName], expand DC= RootDomainName, expand OU
~If your SQL service running under domain user account then select user account.
~If your SQL service running under Local System/Network Service then select computer account.

Right click on above selected object, properties and select attribute name with ‘servicePrincipalName’

 

adsi1


Few points regarding SQL SPN registration:-

  • If SQL services running under Local system/Network Service account then SPN is registered automatically during service startup and de-registered during service stop. Event (26059,26060 ).
  • If SQL services running under domain account that is member of domain administrator group then SPN registered automatically.
  • In most of the environment, it’s recommend to run SQL services under a Domain User Account which has minimal permissions, for those situation we need to register SPN manually.

 


SQL client may start to use NTLM authentication when SPN is NOT registered OR It’s sending wrong set of information that cannot be validated against SPN stored in AD database.

For example:-

  • When SQL service running under local system/network service and SQL client (OR Front end application server) is contacting SQL server using host name which is not present in AD/DNS database (ex. Create a host file entry for name ‘MySQL’ points to IP of SQL server in application server and use ‘MySQL’ for SQL communication).
  • SQL service changed to domain user account but SPN is NOT registered.

 


To understand the root cause & possible solution, we can use the combination of following utilities & tools.

  • Netlogon log.

Enabled NetLogon logging on DC. Open command prompt & execute following command.

Nltest /DBFlag:2080FFFF

net stop netlogon
net start netlogon

Location \windows\debug\netlog.log, please don’t forget to stop logging by following command once your test complete.

Nltest /DBFlag:0x0

When NTLM is used, you may see regular pass through authentication attempts. Ex..

[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Entered
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Returns 0x0
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Entered
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Returns 0x0

More details on NetLogon log

Large set of logs can be review using Netlogging Parase

  • NetMon log.

Enable on SQL server, load standard template-authentication traffic.

When Kerberos is used, you may see Kerberos ticket request in first request.

netmon_kerberos

When NTLM is used, you may see regular authentication call.

netmon_ntlm

  • SQL DMV.
SELECT
         c.session_id,
         c.net_transport,
         c.client_net_address,
         s.host_name,
         c.protocol_type,
         c.auth_scheme,
         s.program_name,
         s.login_name
  FROM sys.dm_exec_connections c
  INNER JOIN sys.dm_exec_sessions s
  ON c.session_id = s.session_id
  -- WHERE HOST_NAME = 'ApplicationServer_OR_RemoteServerName'
  ORDER BY HOST_NAME

Above DMV is most useful tool to know authentication scheme, SQL client host name, login name & client IP…ETC.

Sample Output When Kerberos is used.

dmv_kerberos

Sample Output When NTLM is used.

ntlm

Possible output for auth_scheme column.

SQL – When SQL Server authentication is used.
NTLM- When NTLM authentication is used.
KERBEROS- When KERBEROS authentication is used.

Please note SQL Server will always use NTLM if connecting locally (like SSMS). Kerberos is only used if connecting remotely.

  • Audit Event Id 4624 Under security container.

You may see following event in regular interval indicating which authentication mode is used. Example.

Log Name:      Security
  Source:        Microsoft-Windows-Security-Auditing
  Event ID:      4624
  Task Category: Logon
  Keywords:      Audit Success
  Description:
  An account was successfully logged on.

New Logon:
  Security ID:       MyDomain\app-service-account
  Account Name:      app-service-account
  Account Domain:    MyDomain
  Logon ID:         0x22ffcf
  Logon GUID:       {cbb3efa6-8801-002f-8401-8108e795a1dd}

KERBEROS Auth Mode

Detailed Authentication Information:

Logon Process:                      Kerberos
  Authentication Package:           Kerberos
  Transited Services:  -
  Package Name (NTLM only):   -
  Key Length:                        0

 

NTLM Auth mode

Logon Process:                              NtLmSsp
Authentication Package:           NTLM
 Transited Services:  -
 Package Name (NTLM only):   NTLM V1
 Key Length:                                    128

 


Finally if your directory service team complaints that SQL is generating lots of authentication traffic and above information indicate NTLM is used rather than Kerberos auth scheme due incorrect / missing SPN then one of the following step can be used to fix the problem.

  • Change SQL service account either to ‘Network Service’ OR ‘Local System’ OR ‘Local Service’ from SQL server configuration manager so SPN registered itself during service startup. But it’s not recommended due to security aspect and limited functionality. Service Account Types. sscw_services

 

  • Assign SQL service account to be the member of the Domain Admins group so SPN can be registered for SQL service account automatically, it’s NOT ideal as the SQL account will get access to all the resources of AD environment.
  • If above fixes are NOT desirable then allow SQL service account to dynamically register it’s SPN in AD by giving by them rights ‘readServicePrincipalName’ & ‘WritePrincipalName’ using ADSIEDIT.msc.adsi2_dynamic

Unfortunately this method is NOT supported when AD environment have multiple domain controller OR SQL is installed on Active-Passive cluster due to replication & latency issues. Dynamic SQL-SPN registration

  • Manually assign SPN to SQL service account using SetSPN command line utility. (This can also be done using AdsiEdit) The syntax should be like below.
setspn -s MSSQLSvc/SQL1A.myDomain.local myDomain\sqlsvc

Checking domain DC=myDomain,DC=local
  Registering ServicePrincipalNames for CN=sqlsvc,CN=Users,DC=myDomain,DC=local
  MSSQLSvc/SQL1A.myDomain.local
  Updated object

If you are using customized port then syntax would change slightly.

setspn -s MSSQLSvc/SQL1A.myDomain.local:1433 myDomain\sqlsvc

 


Last but not the least, verify front end application if it using the server name (or it’s alias) that is registered in AD & DNS server. Any alias OR host record created in host file of application server that points to SQL IP address may also cause auth schema to fail over on NTLM as SPN cannot be validate such scenarios.

Microsoft provide Kerberos Configuration Manager tool for SQL Server that can help to diagnose & fix SQL Kerberos issue in large environment.

Once you fix SPN & auth scheme issue, restart SQL service. To clear existing Kerberos tickets you can run command ‘klist purge’

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about SQL Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to contact me directly.

#kerberos, #netlogon, #ntlm, #spn, #sql