Press "Enter" to skip to content

Category: Security

Working with SQL Server Certificates in Powershell

Published 2020-12-16 by Kevin Feasel

Peter Schott walks us through the process of certificate maintenance:

I recently had a need to add certificates to SQL Servers throughout an organization. There were quite a few servers to update and the certificates would need to be generated using a given format. This would include some descriptors for the names, IPv4 address, and ensuring that SQL Server would see the certificate when finished.

I realized this would need some sort of script so reached for PowerShell and the dbatools module. There’s a function in dbatools that supports setting the SQL Server Certificate and I knew that would be useful. But first, I had to generate the certificate itself. I read up on this in PowerShell and there’s no “easy” button for creating a certificate at this time, especially not when you need to add extra properties.  Posts such as this one helped me get started. It works by creating an INF file, then shelling out to “certreq.exe” to generate the CSR file needed to obtain a certificate from a certificate authority. We had need to use the DNS name, the FQDN, and the IPv4 address as part of our certificate request, so I had to adjust my code to handle that.

Click through to see how.

Comments closed

Setting a Default Database for SQL Server Logins

Published 2020-12-01 by Kevin Feasel

Adrian Buckman shows us an issue with using a database other than master for a SQL Server login’s default:

This is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue.

Click through for more details.

Comments closed

Displaying Always Encrypted Data in SSRS with a gMSA

Published 2020-11-18 by Kevin Feasel

Ryan Adams shows how we can view Always Encrypted data in SQL Server Reporting Services when SSRS uses a Group Managed Service Account:

Always Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.

Click through for the solution.

Comments closed

Creating Users in Azure SQL Database

Published 2020-11-06 by Kevin Feasel

Kenneth Fisher takes us through a nuance in adding users to Azure SQL Database:

Awesome! I did say I preferred code didn’t I? I am noticing a slight problem though. I don’t actually have a login yet. So I look in object explorer and there is no instance level security tab. On top of that when I try to create a login with code I get the following error:

Msg 5001, Level 16, State 2, Line 1
User must be in the master database.

Read on for the whole process.

Comments closed

Backing Up the Service Master Key

Published 2020-11-03 by Kevin Feasel

William Assaf takes us through backing up important keys in SQL Server:

You should consider complimentary backup solutions that backup/snapshot the entire server (or VM) for SQL Server, but sometimes these technologies are limited or have too much of an impact on the server. A whole VM snapshot for example that is reliant on VSS could incur an unacceptable long IO stun duration when it occurs. 

Regardless, in all cases, SQL Server backups of each database should be taken regularly. This is a conversation for another blog post but a typical pattern is weekly full backups, nightly differential backups, and in the case of databases not in SIMPLE recovery model, 15 minute transaction log backups.

Read the whole thing.

Comments closed

Using Key Vault in Azure Databricks

Published 2020-10-29 by Kevin Feasel

Arun Sirpal shows us how easy it is to tie Azure Key Vault into Azure Databricks:

The key vault should always be a core component of your Azure design because we can store keys, secrets, certicates thus abstract / hide the true connection string within files. When working with databricks to mount storage to ingest your data and query it ideally you should be leveraging this to create secrets and secret scopes.

Click through for a demo.

Comments closed

Azure Site-to-Site VPN Blocking Certain Traffic

Published 2020-10-20 by Kevin Feasel

Denny Cherry diagnoses a network configuration issue:

I ran across an interesting a couple of weeks ago when working with a client. The client has several subsidiaries each with their own vNet. The client had a site to site VPN been the Azure vNets. All traffic was successfully crossing the Azure Site to Site VPN as expected. The sticking point was that a software licensing server running in one of the subsidiaries Azure infrastructure configurations. The software licensing software simply wasn’t working.

Click through to learn why.

Comments closed

Oracle Error ORA-28353: Failed to Open Wallet

Published 2020-10-09 by Kevin Feasel

Rene Antunez diagnoses an Oracle error:

I noticed the original error after applying the October 2018 bundle patch (BP) for 11.2.0.4. While I realize most clients are no longer in 11.2.0.4, this information remains valid for anyone upgrading from 11.2 to 12, 18 or 19c.

I had been doing several tests on my Spanish RAC (Real Application Cluster) Attack for 12.2. The goal was to patch my client to October 2018 PSU; obtaining enough security leverage to avoid patching their database and do their DB (database) upgrade to 18c. I created RAC VMs to enable testing. I also set up my environment to match the client’s, which had TDE with FIPS 140 enabled (I will provide more details on this later in the post).

While the patching was successful, the problem arose after applying the patch. I was unable to open the database despite having the correct password for the encryption key.

When I first read the title, I thought it was a joke making fun of Oracle’s licensing practices.

Comments closed

Clone Logins and Users with dbatools

Published 2020-10-08 by Kevin Feasel

Jana Sattainathan takes us through a couple of DBA scenarios:

One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be

– Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
– Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases

Read on to see how.

Comments closed