A Fine Slice Of SQL Server
Well…..I just learned about these and thought that it would be good to understand them a little more and have some links to read more about them. I honestly don’t have a lot of SQL 2022 servers in our customers SQL estate, so this has flown under the radar for me. This will be an attempt to put some spread-out information in a one-stop shop.
Click through for a table with information on roles, as well as lists for permissions.
Leave a CommentJoe Celko talks about the lesser-known language in SQL (compared to DML and DDL):
But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.
The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.
Click through to learn more.
Leave a CommentReitse Eskens says the bits must flow:
I had an interesting question lately where I was requested to show all the network traffic within an Azure landing zone. Specifically source and target IP, protocol and port. From the aspect of Zero Trust, it’s important to show both successful and failed connections in your network. To be able to answer this question I had prepared myself by enabling the so-called flow logs on the Network Security Groups (NSG). NSG’s are used to control traffic on the IP and port level between resources. There’s no packet inspection, just a check if IP 1 is allowed to connect to IP 2 on port 3. In this specific case, it also had to do with a migration to Azure Firewall where all the NSG rules had to be validated.
But getting the data is one thing, finding out what is in it is something else. In this blogpost I’ll drag you along the steps I took to get the raw JSON data into a SQL table and analyse the data.
Read on for the process and quite a bit of T-SQL code.
Leave a CommentJerome Robert reviews the papers:
As such, nowadays, almost all developers use some form of AI-generated code — and they absolutely should. AI tools make developers’ lives easier by leveraging the knowledge cultivated by the development community over time and across the globe to overcome obstacles that, while potentially new and challenging to them, have long been addressed. They can reasonably trust that code to perform the function they want to achieve — and can test it to be sure.
But can they trust that code to be secure? Absolutely not. With all that time and work spent committing functional code, just as much, if not more, is spent navigating the security backlog afterward.
Click through for a summary of two recent academic papers, as well as links to the papers themselves.
Comments closedChad Callihan talks about an untrustworthy setting:
TRUSTWORTHY is a database property change that can have far-reaching security consequences when turned ON. Let’s take a brief look at what the TRUSTWORTHY property is and if it’s worth turning on, even when it is a potential fix to your problems.
Chad links to a DBA Stack Exchange post from Solomon Rutzky concerning module signing, which is a good opportunity for me to plug Solomon’s modulesigning.info. This is the correct answer, not TRUSTWORTHY or any of its ilk (EXECUTE AS, cross-database ownership chaining, etc.).
Comments closedGilbert Quevauvilliers grants some permissions:
This blog post explains how to configure access for my Service Principal to interact with the Azure Storage API to use the API to get details for Microsoft Fabric Storage.
This is part of a blog post series where I am going to show you how to “View Total Storage consumed in Microsoft Fabric”
When I started this blog post I realized that I first need to explain how to configure the Service Principal authentication to interact with the Azure Storage API permissions. This is because in my notebook these steps are required for the notebook to run successfully.
Read on to find out how.
Comments closedMIka Sutinen doesn’t need a password:
Managed Identities in Azure provide an efficient and secure way for managing credentials when accessing databases, or other Azure resources. In this post, we build on my recent post about using Service Principal for database authentication, further exploring how we can leverage Managed Identities to simplify the process.While Service Principals can make some parts of the access management simpler in Azure, you’re still left with several responsibilities in their management.
These responsibilities include rotating secrets and managing the lifecycle of the Service Principal, to name maybe the two most crucial ones. And if you have multiple applications, each with a separate Service Principal, this can be complex to manage.
Click through to learn more about managed identities and service principals, and how they work to link together Azure resources behind the scenes.
Comments closedTanayankar Chakraborty reads an audit log:
A recent issue was brought to our attention that customers could not query .xel log files in an Azure SQL DB using t-sql command. The customers complained that when they ran the command, they received column headers but no content whereas they know that there is content in the logs because they were able to open them with SSMS using Merge Extended Event Files. Here was the T-sql command used by the customer:
select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/*.xel’, NULL, NULL);
Click through for the solution, which came down to two separate issues.
Comments closedAs cloud environments become more complex, securely managing secrets and credentials is increasingly critical. Azure KeyVault provides a secure way to store and access secrets in your Azure environment. In this tutorial, we’ll walk through how to create an Azure KeyVault, add secrets, and retrieve them using PowerShell.
Click through for the demonstration.
Comments closedRoberto Yonekawa diagnoses an error:
We had a support request where the customer was getting an error when trying to export his Azure SQL Database to a bacpac file using the SqlPackage command-line utility.
Error message:Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2024-08-21T16:10:56 : Microsoft.SqlServer.Dac.DacServicesException: One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71627: The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12.
Click through for the specific issue Roberto found. I’d imagine that there are other permission sets that are incompatible with Azure SQL Database and would cause this error message to pop up as well.
Comments closed