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 →

How to capture query errors using SQL Server Extended Events

If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors. To use the script you'll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files. I usually place them in the C:\tmp - although be careful just in case you fill the C:\ drive! The alternative is to use another drive. CREATE EVENT SESSION [CaptureErrors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username, sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]>=(10))) ADD TARGET package0.event_file(SET filename=N'C:\tmp\CaptureErrors.xel',metadatafile=N'C:\tmp\CaptureErrors.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1... Read more →

SQL Server Docker and Container Cheat Sheet

I've started working with Docker platform and managing SQL Server Containers. This list are some of the commands I execute most often when using Docker and Containers. --Pull down a container from the Docker registry docker pull --(generic approach)Pull down a sql server container to a valid docker installation on RHEL and run (generic approach) docker pull microsoft/mssql-server-linux docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=LinuxSQLServer123' -p 1433:1433 -d microsoft/mssql-server-linux --(customised approach)Pull down a sql server container to a valid docker installation on RHEL and run Step 1 - Mapping volume names with customised locations **comment - data volume** docker volume... Read more →