August 29, 2024 at 3:17 pm
About 15 years ago we started creating DNS aliases for each database. This allowed us to move databases without needing to change client connections. We now use these aliases in all applications, reporting, ETLs, etc... When we upgrade SQL we create new servers and migrate the databases, using the aliases made this simple. We also use these aliases in linked servers so we have a linked server for each database. All of our reporting and cube connections hit a single database that only contains views and procedures using these linked servers. Not optimal for performance, but centralized and simplified things.
Now we want to implement certs and I was told that every alias used to connect needs to be part of the cert (alternate names). So this would mean adding aliases to the certs and if we created a new database or moved a database the cert would need to be recreated.
Am I understanding this correctly and if so is there a better way to manage this?
August 29, 2024 at 3:52 pm
I think it is one of those "it depends" situations.
The cert would need a static name, but the subject alternate names (SAN's) can allow you to use the cert for other purposes. For example, if the common name for the cert was ServerA, and your SAN was set up with multiple like: ServerA.corp.com, ServerA.internal.com, SQLServerA, SQLServerA.corp.com, then the cert is valid for ALL of the entries.
So, if your DNS alias was set up for SQLServerA.corp.com to point back to ServerA currently and you migrated from ServerA to ServerB AND updated the DNS alias to point back to ServerB instead of ServerA, the cert would still be valid.
There are things that can make the cert invalid though, such as invalid CA (certificate authority), untrusted CA, improper cert configuration (missing required roles in the certificate), expired certificate, and revoked certificate. Possibly others, but those are the big ones I've been hit with. Invalid CA and untrusted CA are common with self-signed certificates.
But your understanding is partially correct - any time a new alias was added to DNS, you'd need to update the cert to include the new alias. The only exception here would be if you have a wildcard certificate, but I would advise against those.
If you MOVED the database, as long as the DNS Alias matched what was in the certificate SAN list AND the new server trusts the CA, no new cert should be required.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 3, 2024 at 5:12 pm
Thanks, appreciate the info!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply