A Fine Slice Of SQL Server
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 closedKenneth 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 closedJana Sattainathan shows how to manage file permissions via Powershell:
Let us say ModelFile.txt has the right permissions and MessedUpFile.txt had certain permissions missing. The easiest way to copy the permissions from the former to the latter is using the PowerShell *ACL* (Access Control List) commands.
But Jana has us covered in other situations as well, so read the whole thing.
Comments closedWilliam 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 closedArun 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 closedDenny 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 closedRene 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 closedJana 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 closedTibor Karaszi warns us to be wary of the securityadmin role:
Over the last week, I’ve seen two discussions where the securityadmin server role was suggested.
The scenario is typically that we don’t want to make this person a sysadmin, but we want this to manage logins and stuff like that.
But Tibor explains why you should watch out—my rule of thumb is to treat securityadmin as just as important as sysadmin. It’s not the same, but someone with securityadmin can do a lot of damage to a server.
Melissa Coates has some guidance for us:
The Power BI administrator role is a high-privilege role which should be carefully managed. As with many aspects of administration and governance, this involves having a balance between people being able to get things done and risk of when too many people having elevated permissions.
Click through for the blog post as well as a video.
Comments closed