Monthly Archives: November 2015

Reset a lost or forgotten SA password

— By Lori Brown  @SQLSupahStah

Any login that is a member of the sysadmin role can reset the SA password. But, what if you have a hostile takeover by an application or for some strange reason don’t have any other logins as members of the sysadmin role?   Don’t laugh!  I have actually run into both of these situations.  Most places simply lose or forget what the SA password is and then are in a quandary on how to change it.  Here is the way to do it.  It does require that an outage be scheduled because you will have to stop and restart SQL.  The account that is making this change must be a member of the local administrator Windows group.

 

Stop both the SQL Server and SQL Agent services.  I find it is best to do this from command line.  (A couple of notes for the instructions below that involve NET STOP or NET START…. Use mssqlserver for a default instance or mssql$<<instancename>> for a named instance.  Use sqlserveragent for a default instance or sqlagent$<<instancename>> for a named instance.)

 

NET STOP sqlserveragent

NET STOP mssqlserver

Blog_20151119_1

Next, start SQL Server in single user mode either from command line or from the Configuration Manager GUI and tell the application that only SQLCMD can connect while it is in single user mode to prevent applications or other users from connecting before you can.  If you use Configuration Manager add –mSQLCMD to the SQL Server startup parameters as shown below.

Blog_20151119_2

Or, here it is if you do this from a command prompt and use SQLCMD to execute an ALTER LOGIN statement to change the password:

 

NET START mssqlserver /m SQLCMD   

 

ALTER LOGIN sa WITH PASSWORD = ‘Str0ng9@ssw0rd’

GO

EXEC sp_addsrvrolemember ‘SQLRX\lorib’, ‘sysadmin’

GO

Blog_20151119_3

Type in exit when you want to exit SQLCMD.

 

Next, stop SQL Server again and restart both the SQL Server and SQL Agent services.  Make sure if you stopped and started the services from the Configuration Manager that you remove the –mSQLCMD from the SQL Server startup parameters.

 

NET STOP mssqlserver

 

NET START mssqlserver

NET START sqlserveragent

Blog_20151119_4

Once the services are restarted, you should now be able to connect as sa as well as with a domain account that has sysadmin access to the instance.  Now…Please go document your new sa password after this….you don’t want to have this happen again, do you?

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!

Good DBA! Tasks that all DBA’s should do to have a Stable SQL Server

— By Lori Brown  @SQLSupahStah

What are the critical aspects of SQL Server that a DBA should check on a daily basis?  How about on a weekly or monthly basis?

Daily Checklist

Backups:

Check your backups (Full, Differential, Transaction Logs) to validate that they were successfully created per your company’s policies.

Nightly Processing:

Review the nightly or early morning processes for success or failures and troubleshoot as needed.

DR\HA:

Review the status of Cluster nodes, Log Shipping, Replication, AlwaysOn or Mirroring on both the primary and secondary instances and troubleshoot as needed.  If a failover occurs, investigate root causes immediately.

Logs:

SQL Server Error Log

Review the SQL Server error log for any unexpected errors, warnings of bad performance or security issues.

Windows Event Logs

Review the Event Logs at a minimum to find out if any Windows or hardware related errors or warnings are being written.  Most hardware vendors write warnings to the Event Logs when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid-day emergency.

SQL Server Agent Jobs:

Review for failed SQL Server Agent Jobs.

Storage:

Validate you have sufficient storage on your drives to support your databases, backups, batch processes, etc. in the short term. Track your file growth and project when you will need to add space as drives fill up.

Weekly or Monthly Checklist

Backup Verification:

Verify your backups and test on a regular basis to ensure the overall process works as expected.  Validate that no error messages are generated during the restore process.  At bare minimum use the RESTORE VERIFYONLY statement to verify that backup files are usable.

Windows, SQL Server or Application Updates:

Check for service packs/patches that need to be installed on your SQL Server from hardware, OS, SQL or application perspectives.

Fragmentation:

Track how often tables are defragmented.  Review the fragmentation info for your tables to determine if the fill factor is too high. Be aware of forwarded records on heap tables and be prepared to remove them.

Maintenance:

Perform database maintenance on a daily, weekly or monthly basis. This includes index rebuilds, reorgs, statistics updates and integrity checks.

Performance:

Track processor, memory, disk performance and free space, network and key SQL Server performance and log performance counters.   Review these counters to determine if there are any aspects of the hardware that may have reached capacity.  Be very aware of Page Life Expectancy to detect memory pressure.  Check file sizes of any database with multiple files in a filegroup including tempdb to make sure that all are the same size.

Security:

Remove unneeded logins and users for individuals that have left the organization, had a change in position, etc.

Configuration:

Review the SQL and database configurations for changes.  Make sure that all configuration options set are in alignment with your company’s policies or best practices.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!

SQL 2014 Backup Encryption

Available in SQL Server 2014 Enterprise Edition and Standard Edition

Backup encryption has been introduced in SQL 2014 to allow encrypting backups without being forced to encrypt the entire database. Several encryption algorithms are supported, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You can also compress the encrypted backup. Encryption will cause higher CPU utilization so you must make sure that CPU’s can handle backup along with workload.

Here is what to do to encrypt your backup:

  1. Create a master key with a password
  2. Create a certificate or asymmetric key
  3. Then backup the database and set your algorithm and the certificate that was created.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MK_Pwd_123!’;

GO

–Create a certificate or asymmetric key

USE master;

GO

CREATE CERTIFICATE Bkup_Cert_for_My_DB

WITH SUBJECT = ‘Backup Cert For My Database’

GO

–Backup the database

BACKUP DATABASE [MYDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MYDB.bak’

WITH

COMPRESSION,

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = Bkup_Cert_for_My_DB

),

STATS = 10

GO