A Fine Slice Of SQL Server
Kenneth Fisher puts on a mask:
I use impersonation a lot. It’s a really easy way to check if someone has the permissions they are supposed to. That said, a co-worker recently had an interesting question. They were testing permissions on a synonym.
Msg 916, Level 14, State 1, Line 3
The server principal “Domain/NetworkName” is not able to access the database “OtherDB” under the current security context.
Read on to see what caused the issue and how you can fix it.
Comments closedMirek Sztajno has an interesting announcement:
Enabling Azure AD authentication opens access to the Azure cloud identity system. Azure AD is used by many cloud services and unifies all local authentication mechanisms used by Microsoft products providing one central identity repository and authentication management system available to different platforms, including Azure SQL and SQL Server on-premises. The variety of available authentication methods including single sign-on (SSO) and multifactor authentication (MFA), provides strong security support in the authentication area for different services used internally by Microsoft and by external customers. Azure AD authentication is the recommended authentication method for Azure SQL and SQL Server.
Looks like it does require Azure Arc, which has a fairly small per-instance monthly charge. Click through for the details. That said, you will be able to use this feature on-premises and in other clouds, not just in Azure VMs.
Comments closedGabi Lehner announces a change:
The
current_principal_is_member_of()function checks if the principal who runs the query is a member in any of the users, apps or groups provided as arguments.Up until now, it was allowed to specify the AAD group details in multiple forms, including the display name of the AAD group, without specifying the tenant id or name, for example
current_principal_is_member_of(“mygroup”).
I have to say, that’s a pretty big security flaw.
Comments closedAndrew Coughlin sets up secure Key Vault access:
Let’s first discuss the setup of what we will be discussing in this blog post. I will have two subscriptions assigned to the same Azure AD Tenant. Within each Azure subscription I will have a resource group in each. I will create the Azure Key Vault in one subscription / resource group and then I will create a virtual machine in the other subscription / resource group. This is just for example purposes; I could utilize other azure services that can use managed identities. I could also create a service principal for my application to use to get keys or secrets.
In this example we would be using private endpoints. Are you looking for how to do this with public endpoints? Check out my recent post on how to do that here .
When in doubt, private endpoints are the right choice. They’re probably the right choice when not in doubt as well.
Comments closedGarry Bargsley sorts out an e-mail problem:
Recently, I was tasked with creating an email subscription to a new SSRS report in an environment that I was not familiar with. I have created my fair share of subscriptions in my day, and this one was very straightforward.
I found the report, clicked on Manage, and went to the Subscription page. Clicked on New Subscription and filled in all the information, easy peasy.
The subscription is ready to go when the schedule kicks in the next day, or so I thought.
Turns out that wasn’t quite the case. Read on to see what happened and how Garry fixed the problem.
Comments closedCraig Porteous reviews a couple of announcements from Data + AI Summit:
Having worked with many organisations across different industries and sectors, the sharing of data with partners and vendors is always a pain point and one that all too often results in both parties not quite getting what they want or need. This isn’t restricted to my experience however which is why Databricks announced Delta Sharing back at DATA + AI Summit 2021.
Coming to this year’s conference, Delta Sharing has been established as the foundation for many new features with the announcement Databricks Marketplace and Cleanrooms for example, both built upon the Delta Sharing protocol. We’ll explore Cleanrooms below and I’ll look at the Databricks Marketplace in it’s own post.
Read on for Craig’s thoughts on two of the bigger announcements at this year’s summit.
Comments closedTracy Boggiano opens a can of worms:
So, what to rant about? Should your managers have access to your SQL Server instances? It depends. You say on what. Well, just one thing. What type of manager do you have?
Read on for Tracy’s thoughts on the matter. Speaking as a manager, I tend to agree. If you don’t know what you’re doing, better not to have the ability to mess things up. I can think of oddball scenarios where you might still want the manager to have (at least theoretical) access to a system, primarily as a backstop in case the line staff get locked out or someone gets hit by the lottery bus and you suddenly need to bring in a new employee. That’s more of “break glass in case of emergency” access, though.
Comments closedMala Mahadevan recounts a horror story:
Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins.
But wait, it gets worse.
Comments closedTom Collins compares and contrasts:
A common question from developers & data owners is what benefits does Always Encrypted offer over column level encryption aka cell level encryption? First thing to understand is what are the basic differences between the two methods – Column-level encryption vs Always encrypted
For as much as I appreciate Always Encrypted, it seems I use column-level encryption about an order of magnitude more often.
Comments closedTom Collins needs to encrypt some data:
Using a symetric key , how can I encrypt a column in a SQL Server database table? And what are some of the common pitfalls related to setting up column level encryption
Read on for the process. Even with other options available like Always Encrypted, I keep coming back to this because it’s simple, contained in-database, and works well.
Comments closed