Reset SQL Server SA Password on Linux

The “sa” account is a critical account on all versions of SQL Server. If you forget your “sa” password, it can cause problems. This article will help you identify the steps required if you have forgotten the password of your “sa” account of your SQL Server instance on Linux.

The mssql-conf utility is a configuration tool that installs with SQL Server on Linux (Red Hat Enterprise Linux, SUSE Linux Enterprise server, and Ubuntu). This handy tool as a replacement for SQL Server Configuration Manager which exists when you install SQL Server on Windows.

Reset the “sa” account password 

  • Connect SQL Server using command-line tool with the existing password to make sure that your current password is working. You will skip this step if you don’t know the password.
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • To change the “sa” password, first stop SQL Server service on Linux:
sudo systemctl stop mssql-server
sudo systemctl status mssql-server
  • Reset the “sa” password by creating a new strong password :
/opt/mssql/bin/mssql-conf set-sa-password

Note: When you are resetting/changing “sa” password using sqlcmd in a bash terminal,  you must not use the special character “$”. 

  • Start and verify the status of SQL Server Service:
sudo systemctl start mssql-server
sudo systemctl status mssql-server

  • Connect SQL Server with the new password:
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • Once you are successfully connected, you know the new password is correctly altered.

Change password using sp_password

  • Login with any User account that has sysadmin access on the server.
  •  syntax: sp_password NULL, ‘<insert_new_password_here>’, ’sa’
  • command :-

sp_password NULL, 'Admin@1433', 'sa'

  • Log into the SQL Server instance using the “sa” account and the new password to verify it has been successfully changed.
Advertisements

Common Database Design Mistakes

Project Management

When creating a new database instance, people will often make mistakes. While I can’t list all the mistakes that people can or will make, I hope this brief list will help you know what mistakes are possible, and help guide you to not making as many mistakes. Sometimes we attack a design problem with the idea that we will just get the work done, but most times it is better to take the extra time to do it right.

I’m not perfect, and I have made these (any many other) mistakes in database design. I’m not trying to tell you what to do or even how to do it. I’m just trying to take my lessons learned and provide a simple list so that you might not make the same mistakes. I also want to point out that no list will ever be the only way to do anything. With database design questions, the best answer is usually “it depends”. When considering the many variables that make up your environment, you will need to make many decisions that help your database instance work best in your unique environment. You have to take into account the personnel you are working with, limits of your hardware, company policies, etc.

Continue reading “Common Database Design Mistakes”

Rebuild and Restore Master Database in SQL Server

database - @SeniorDBA

The Master database is important to your SQL Server instance. This database records all the system-level information for your SQL Server system. This includes instance-wide metadata like logon accounts, endpoints, linked servers, system configuration settings, and records for the existence of all other databases and their file locations.

SQL Server cannot start if the Master database is unavailable. The core information of master database is recorded in a physical file called master.MDF files, and the transaction logs are stored on to the masterlog.LDF file. This means the all user and login details for the instance and the information about all the other databases on that instance are stored on the master database.

If anything happens to the master database you can’t start or use your SQL Server instance. As a Database or System Administrator, it is essential that you to know the symptoms that may indicate the corruption or damage in your Master database so you can troubleshoot those types of issues correctly.

Continue reading “Rebuild and Restore Master Database in SQL Server”

How to Create a SQL Server Logon Trigger

Login - @SeniorDBA

You may want to log the event each time a user logs into your database. This can be easily done by starting SQL Server Managements Studio (SSMS) and running the following code in Transact-SQL: Continue reading “How to Create a SQL Server Logon Trigger”

Transaction Log File Size and VLF in SQL Server

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

Continue reading “Transaction Log File Size and VLF in SQL Server”

Building Games Using SQL Server

You can build an interesting game with almost any computer language. Building a game using Transact-SQL seems to be the biggest challenge, but it has been done before. I wrote about this subject before, but there is now a new effort by Daniel Janik as described in this article.

Continue reading “Building Games Using SQL Server”

SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • DROP DATABASE IF EXISTS
  • Columnsets (and Sparse Columns in general)
  • SQLCLR
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • DBCC PINTABLE
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups
  • SQL_VARIANT

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.