SQLServerCentral.com Community Update for {date}

I read an interesting blog from the cyber security team at Microsoft, noting you should use TLS for your SQL Server connections. I would assume most professionals know that using TLS and secure protocols across the network is important. I would also assume few of us are willing to get real certificates for all of our SQL Servers, especially those in dev and test environments.

Setting up a certificate for a server instance isn't hard, but it's also not easy. It's also something that I don't know how easy would be to automate in many environments. I know that you can use APIs from somewhere like Let's Encrypt, but integrating that into a server setup process would be something. What about integrating this into instances in containers? I don't know that I think many SQL Server organizations have DNS integrated for most of their database servers, much less asking them to also get certificates set up.

I do think that Microsoft likely has better infrastructure in place, and better staff resources, than most of us. I can see this being something needed for Azure SQL DB and Azure MI. Heck, this might be something we want for all services that we get in the cloud. Since we don't control much of anything other than the service, and Microsoft can automate the process of generating certificates, this makes sense.

I think this might be something that is a good idea for production systems as well, ensuring that when a DBA or system makes a privileged connection to a database server in production, the person or process is sure of which database server is at the other end of the connection. I don't know that I've seen an exploit in the wild where someone impersonates a prod server and captures credentials in some man-in-the-middle attack, but I wouldn't be surprised if it happens.

Do you think you also ought to avoid trusting the server certificate? I don't think this is practical in many orgs, but I'd be curious what you think today.

CREATE LOGIN way0utwest WITH PASSWORD = 'horsestakework'; GO CREATE USER way0utwest; GO CREATE OR ALTER PROCEDURE SillyProc AS BEGIN EXECUTE AS LOGIN = 'way0utwest'; END GO EXEC SillyProc; SELECT SYSTEM_USER; REVERT;

CREATE TABLE moduletest( col1 int, col2 int, col3 int) GO CREATE OR ALTER PROC usp_getTest AS SELECT col1, col2, col3 FROM dbo.moduletest GO --run once to get proc in cache EXEC dbo.usp_getTest; GO

Today's question (by Steve Jones - SSC Editor):
	Changing Context in Procs
I log into a SQL Server 2019 database with my AD account, named 'dkr\Steve'. I then execute this code: CREATE LOGIN way0utwest WITH PASSWORD = 'horsestakework'; GO CREATE USER way0utwest; GO CREATE OR ALTER PROCEDURE SillyProc AS BEGIN EXECUTE AS LOGIN = 'way0utwest'; END GO EXEC SillyProc; SELECT SYSTEM_USER; REVERT; What is returned by the SELECT?
Think you know the answer? Click here, and find out if you are right.