List the differences between Kerberos and NTLM

There is a message found in SQL Server Error Logs similar to The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. The line I'm interested in reviewing is Failure to register a SPN might cause integrated authentication to use... Read more →

How To Use Transparent Data Encryption To Increase Your Security

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection. Setting up TDE in SQL Server is in summary Create a master key. Create or obtain a certificate protected by the master key. Create a database encryption key and protect it by using the certificate. Set the database... Read more →

Use Get-ADPrincipalGroupMembership with other Active Directory domains

Question: I use the Powershell cmdlet Get-ADPrincipalGroupMembership to get AD group membership of IDs within the current domain, but how can I use Get-ADPrincipalGroupMembership to check the membership of an ID in another domain? Answer: Yes , The Get-ADPrincipalGroupMembership cmdlet will return the group membership of the specified user , in the current domain of the logon executing this cmdlet . To access the group membership of a specific ID , you'll need to use the -server switch . Example Get-ADPrincipalGroupMembership 'ID1' -Server 'anotherdoamin' | select name If you want to read on how to access the group membership of... Read more →

How to find list of AD groups of a user from SQL Server

An application owner reported a sudden loss of access to database tables . They'd been reading from the sql tables - but logged off for awhile , logged back on and couldn't see the tables. I logged on , could see the tables , so discounted any possibilities they may have been dropped. So the next question was to confirm the user logon still had the relevant privileges to see and read from the tables. I needed to check the user was in certain Active Directory Groups. I wanted to check this from the SQL Server side - and one... Read more →

How To Automate SQL Server Vulnerability Assessment

Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced security pane . The powershell cmdlet - SQLVulnerabilityScan - is available through the SQL Server modules , the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. The first step is to check that you can execute the cmdlet , point to a SQL Server and extract the report from the Vulnerability Scan. This powershell snippet - connects to a SQL Server and runs the scan over a specific database... Read more →