How to measure Transparent Data Encryption (TDE) Performance

Question: What is the performance impact of enabling Transparent Data Encryption (TDE) on SQL Server database query performance? How can I measure the performance impact ? Answer: There is a general outline about the pros and cons of different Encryption on Encrypting data in SQL Server . So once you\ or your organisation has made the decision to enable TDE for SQL Server databases . it's time to assess the impact : performance , volume and DBA management overhead. According to the Microsoft documentation "TDE has an estimated performance impact around 3-5% and can be much lower if most of... Read more →

Find Active Directory groups with Get-ADGroup search filter

Question: How can I find a list of AD groups with a specific search filter on the name column. ? In this case I want to find all AD groups in the default OU group with a search string. Answer: Assuming you are in the current Domain group \ OU group , find an example of Get-ADGroup which will filter on the search string "admin" Get-ADGroup -Filter "name -like '*admin*'" -Properties * | select name if you need to commit the Get_ADgroup search onto another server - than you'll need to use the -server switch Get-ADGroup -Filter "name -like '*admin'"... 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 →

How to create a PSCredential object

Creating a Credential Object is useful when you require to pass a username and password through Powershell commands. You might see Credential used in various Powershell cmdlets - such as SqlVulnerabilityAssessmentScan. An example of invoking the SqlVulnerabilityAssessmentScan may be something like: Invoke-SqlVulnerabilityAssessmentScan -Credential "sa" -ServerInstance myserver\inst1 -DatabaseName "master" The problem with the way Credential is used above - will create a request for the password. What might be more efficient is to create a secure object with username \ password Enter the PSCredential. A PSCredential object represents a set of security credentials, i.e username and password. Here is an example... Read more →

Error Message: The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Question: When attempting to expand the Catalogs in the linked server object defined, I received below message.How can I fix this problem? The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’ Answer: The overall fix is to grant execute rights to the logon requiring the privileges on SYS.XP_PROP_OLEDB_PROVIDER. Step 1 >> Map the user in the master database USE [master] GO CREATE USER [myuser] FOR LOGIN [myuser] Step 2 >> GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [myuser] These are the basic steps. But quite often in an organizations SQL Server Security Policy , direct execute... Read more →