A really great feature in Azure SQL DB went GA today. That feature gives you and SQL DB the ability to automatically fail databases over to a Secondary replia, without having to configure your application to handle that failover. You point your application at a VIP and that VIP will automatically handle failover of the resource.
Say for example you have a database in US West named db1-west.database.windows.net and the DR copy of it in US East named db1-east.database.windows.net. This feature lets you create the VIP db1-vip.database.windows.net which automatically points to whichever database is currently active. In the event of a failover of US West, the VIP is going to failover to the database in US East, the database in US East become writable and when the US West is back up, the data will sync back.
Another cool thing which this feature does is something that most features won’t do, it’ll trigger a failover that allows for data loss. Now, this normally would be a very dangerous thing, but the Azure team has come up with a safe way of doing it. When you figure the service to do the failover, you decide how long you want to wait for there to be no data loss. If you want the system back up as soon as it allows for, select the smallest number, otherwise select a larger number. This allows you, and the business unit that you support, to decide what level of protection you want to have built into the system.
If you are thinking about moving to PaaS, not being able to have a DR option may have been stopping you. This is no longer a blocking point, you now have an easy to configure DR, that you can manually failover is need be. If you’re thinking of moving to Azure, DCAC can help to plan and execute that migration. Contact us today, to schedule a meeting to discuss if the cloud is right for you.
The post General availability: SQL Database auto-failover groups – and it looks awesome appeared first on SQL Server with Mr. Denny.
Setting up Transparent Data Encryption, or Backup Encryption, or basically anything else where you need to restore a certificate to another SQL Server instance can get tricky as soon as you run the instances under different service accounts. The problem that people will usually run into is that all the example code assumes that all the instances are running under the same service account, but this isn’t always the case in the real world. So let’s review the code that Nic posted to Stack Exchange earlier today.
/* Server 1 */
/* Create the master key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = ‘Backup Encryption Certificate’;
/* Backup the master key */
BACKUP MASTER KEY TO FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
ENCRYPTION BY PASSWORD = ‘SomeRandomPwd’;
BACKUP CERTIFICATE BackupCert TO FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShareBackupCert.pvk’,
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);
/* Server 2 */
/* Create master key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShare\BackupCert.pvk’,
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);
–Msg 15208, Level 16, State 6, Line 32
–The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
/* Try restoring the master key instead */
DROP MASTER KEY;
RESTORE MASTER KEY FROM FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’;
–Msg 15317, Level 16, State 2, Line 39
–The master key file does not exist or has invalid format.
Now as we can see, the creation of the certificate failed, but only when running each server under its own service account. The question becomes why.
The answer is depressingly simple, NTFS permissions. By default SQL Server will make it so that the backup of the certificate is only available to the account that is running the instance that creates the certificate backup. So if you need to restore the certificate using the CREATE CERTIFICATE syntax shown in the example you’ll get an error. If you change the permissions so that the second SQL Account can read the certificate backup file you created then the restore will work.
The post Exchanging SQL Certificates with different service accounts can be tricky appeared first on SQL Server with Mr. Denny.
Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database–theoretically this makes a database much easier to move between servers (you’ll note the absence of SQL Agent jobs from this post, that’s a difference problem.). One of the biggest benefit is that it allows database level authentication, so there no need to have user logins on server level.
Contained database now enables us to make a database more portable. I can backup and restore to any instance of SQL Server and the database will carry all its logins with it. There is no longer a need to script out all logins and create those at the server instance level for a user to connect to that restored database. I personally have run into the issue of missing logins at the instance level when restoring to another server. In these cases, I have had to go back and script out those logins to apply them to the new instance. You can see how in an emergency where the source server may not be available that not having access to those logins could present a real issue. This is also beneficial for databases that are members of Always On Availability Groups–you don’t have to create logins on each server.
In addition to portability contained databases allow us to expand control of login creation to more than just the database administrator or highly privileged user accounts. Traditional databases require you to create server level roles and server level permissions in order to grant database rights to a user. With contained databases you avoid this, database owner and users with an ALTER ANY USER permission can now control access to the database. One drawback is the database user account must be independently created in each database that the user will need which add a little more maintenance.
Below will show you how to enable this option at both the server and database levels. From there I will show you how to create user logins and what the difference is between traditional (non-contained) login accounts and contained users.
Enable at SERVER level
EXEC sys.sp_configure N'contained database authentication', N'1'
RECONFIGURE WITH OVERRIDE
Enable at DATABASE Level
Note the word “Partial” in the dropdown and script
The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
Use the sys.dm_db_uncontained_entities and sys.sql_modules (Transact-SQL) view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.
ALTER DATABASE [AdventureWorks2016CTP3] SET CONTAINMENT = PARTIAL WITH NO_WAIT
To Add a User
Below you will note a few differences in syntax Traditional used the work LOGIN while Contained used USER. Also note that when adding or changing database permissions the ALTER statements are very difference. Traditional uses ROLE and MEMBER will Contained uses AUTHORIZATION and SCHEMA
Traditional NON-Contained, adding user and granting READ/WRITE to a database
CREATE LOGIN JoeShmo WITH PASSWORD = '1234Password';
CREATE USER [JoeShmo] FOR LOGIN [JoeShmo]
ALTER ROLE [db_datareader] ADD MEMBER [JoeShmo]
ALTER ROLE [db_datawriter] ADD MEMBER [JoeShmo]
Contained Database adding user and granting READ/WRITE to a database this works for both SQL Authentication and Windows.
CREATE USER JoeShmo WITH PASSWORD = '1234strong_password';
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [JoeShmo]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [JoeShmo]
If changing to Contained database and you want to convert all your Server Logins to contained database users Microsoft has given us a great script use. I have reposted it below. The example must be executed in the contained database.
DECLARE @username sysname ;
DECLARE user_cursor CURSOR
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
FETCH NEXT FROM user_cursor INTO @username
WHILE @@FETCH_STATUS = 0
@username = @username,
@rename = N'keep_name',
@disablelogin = N'disable_login';
FETCH NEXT FROM user_cursor INTO @username
CLOSE user_cursor ;
DEALLOCATE user_cursor ;
Aside from the lack of support for MSDB, the one other issue I’ve run into with contained databases was an application that contained multiple databases supporting the applications but used SQL logins. In this case, it was a version of dynamics–with Windows logins this is easy–you simply create the login in each database and let Active Directory deal with the passwords. However, with contained databases the passwords are local to each database–so it’s a challenge to sync these accounts. With my current customer in this situation, we’ve reverted to server logins and used Dbatools to sync the passwords between servers.
I can think of many ways contained database can add benefits, I can’t wait to play around with it more.
Contained databases do not support replication, change data capture, or change tracking. Confirm the database does not use these features.
On Monday November 5th, 2018 I’m thrilled to say I’ll be presenting a precon at the PASS summit. This years precon will be on database security.
The abstract from the PASS website:
With increased focus on data breaches, security issues, and privacy laws, organizations are feeling greater pressure to provide sufficient data protection. GDPR brings substantial fines and potentially criminal prosecution. Are you ready? Are your database systems ready?
During this full day Precon, we will review a variety of ways to secure your SQL Server databases and data from attack. In this session, we will review proper network designs, recommended firewall configurations, and physical security options. We will also review data encryption options, password protection options, using contained databases, and Always On Availability Groups security.
There will also be discussions about additional measures which should be taken when working with Internet-facing applications. From there we will move to the most common attack vector: SQL Injection. We will include all the ways that attackers can use SQL Injection to get into your system and how to protect against it. The security options for database backups is the next topic on the list followed by proper SAN security designs. We will then finish up by reviewing the auditing options which are available against a database and how they can be used to monitor everything else which we’ve just talked about during the day.
There are no prerequisites to attend the precon, so anyone can attend, no matter their experience level with SQL Server (some experience with SQL Server would be helpful).
There are already 100 seats sold, but the room will hold plenty of more people, so there are still seats available. However don’t wait too long, as you could end up waiting for the pre-cons to be completely sold out. So book now, while there are still seats available.
I’ll see you at the PASS Summit,
The post Monday November 5th, 2018 Brings My PASS PreCon on Security appeared first on SQL Server with Mr. Denny.