Get Ahead of Year-End with Bulletproof Database Solutions

By bradyupton in Maintenance, Monitoring, Security, SQL Freelancer

As we approach the final stretch of the year, businesses across all industries are gearing up for the increased demands that come with Q4. From handling higher transaction volumes to meeting year-end reporting requirements, your SQL databases will be put to the test. At SQL Freelancer, we understand the critical role that a robust, secure, and optimized database plays in ensuring your operations run smoothly during this peak season.

Why Your Q4 Success Depends on Your Database

The last quarter of the year can be a make-or-break time for many businesses. Whether you’re dealing with a surge in online orders, closing out the fiscal year, or preparing for tax season, your database needs to perform at its best. Any hiccups in your database can lead to delays, data loss, or even security breaches, all of which can severely impact your bottom line.

This is where SQL Freelancer comes in. Our Q3 marketing campaign, “Prepare for Q4 with SQL Expertise,” is designed to help you proactively address potential issues before they arise. By partnering with us, you can ensure that your SQL solutions are ready to handle whatever Q4 throws your way.

What We Offer:

  • Comprehensive Database Audits: We’ll perform a thorough analysis of your current database setup, identifying any weaknesses or areas for improvement.
  • Security Enhancements: Protect your data from breaches with our advanced security measures tailored to your specific needs.
  • Performance Optimization: Ensure your databases run efficiently under heavy loads, reducing latency and improving user experience.
  • Scalability Planning: Prepare for growth by ensuring your databases can scale seamlessly with your business needs.

Don’t Let Your SQL Databases Fall Behind

As you gear up for Q4, don’t let your SQL databases be an afterthought. With SQL Freelancer by your side, you can be confident that your solutions are secure, optimized, and ready for anything. We’re committed to helping you prepare, protect, and excel—so you can focus on what you do best.

Partner with the Leader in SQL Solutions

At SQL Freelancer, we pride ourselves on being a leader in SQL solutions. Our team of experts is dedicated to providing top-notch service, tailored to your unique business needs. Don’t leave your Q4 success to chance—partner with us and get ahead of the curve.

Ready to get started? Contact us today to learn how we can help you prepare for Q4 with bulletproof database solutions.

Enhanced patching for SQL Server on Azure VM with Azure Update Manager

By bradyupton in Azure, Security 0 Comment

With Azure Update Manager, unlike with the existing Automated Patching feature, you’ll be able to automatically install SQL Server Cumulative Updates (CUs), in addition to updates that are marked as Critical or Important.    

Azure Update Manager is a unified service that helps manage updates for all your machines. By enabling Azure Update Manager, customers will now be able to:   

  • Perform one-time updates (or maybe Patch on-demand): Schedule manual updates on demand 
  • Update management at scale: patch multiple VMs at the same time 
  • Configure schedules: configure robust schedules to patch groups of VMs based on your business needs:  
  • Periodic Assessments:  Automatically check for new updates every 24 hours and identify machines that may be out of compliance  

thumbnail image 1 of blog post titled Announcing public preview of enhanced patching for SQL Server on Azure VM with Azure Update Manager

 

Read more here…

Create Always Encrypted Certificate

By bradyupton in Always Encrypted, Auditing and Compliance, Database Design, Security 0 Comment

In SSMS, browse to Database, Security, Always Encrypted Keys, right click Column Master Keys and click New Column Master Key:

In the New Column Master Key box you can choose from several key stores. The most popular stores are Current User or Current Machine. See below for description.

Local machine certificate store
This type of certificate store is local to the computer and is global to all users on the computer. This certificate store is located in the registry under the HKEY_LOCAL_MACHINE root.

Current user certificate store
This type of certificate store is local to a user account on the computer. This certificate store is located in the registry under the HKEY_CURRENT_USER root.

Be aware that all current user certificate stores inherit the contents of the local machine certificate stores. For example, if a certificate is added to the local machine Trusted Root Certification Authorities certificate store, all current user Trusted Root Certification Authorities certificate stores also contain the certificate.

In this example, we’ll use Local Machine. Type a name for the Master Key, choose Key Store and click Generate Certificate.

Click OK and a new Column Master Key will appear:

Do the same for any other master keys you would like to create. For example, Developer:

To create a Column Encryption Key, right click on Column Encryption Key and choose New Column Encryption key:

Give the key a name and select the Column Master Key that you would like to associate it with:

Do the same for the Developer keys. You should have two column master keys and two column encryption keys:

To view Local Machine certificates:

Open Certlm.msc

Browse to Personal, Certificates:

Double click a certificate and go to the Detail tab where you will see the thumbprint:

Match this thumbprint with the Column Master Key in SSMS:

Right click the certificate in Certlm.msc and choose Properties. Enter a friendly name to identify which certificate belongs with the Column Master Key.

To Export and certificate, right click and choose All Tasks, Export:

SQL Server User Defined Server Roles

By bradyupton in Security 0 Comment

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at the server level until SQL Server 2012.

In this post I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master 
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Create a Server Role in T-SQL

To create a server role called “juniordba” use the following:

USE master
GO
CREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master 
GO
ALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven’t added any permissions to the server role, so Brady shouldn’t have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Click here to view the rest of this post.

Securing and protecting SQL Server data, log and backup files with TDE

By bradyupton in Security 0 Comment

In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.

To setup TDE we’ll need to run a few scripts: (My test database is named TDE)

The following script will create the master key with a specified password ElephantRhin0:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
GO

Next, we’ll create a certificate named TDECert that will be protected by the master key:

USE master;
GO
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
GO

After creating the certificate we’ll backup the certificate to a specified source:

USE master;
GO
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH 
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
GO

Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:

USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

The final step is to set our database to use encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;
GO

If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'TDE'

SQL Freelancer SQL Server Transparent Data EncryptionClick here to view the rest of this post.