Introduction

This article is about how to proceed when you get error message 'Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'.. '

Problem Definition

SA Password is entered wrong more than three times then SA account will be locked or someone wants to reset the password. 

Solution

Using mssql-conf setup  need following step:- (this require reboot SQL Server)

1. Conf setup

Yyou need to stop mssql-server  using this command   -conf setup command 

sudo systemctl stop mssql-server

sudo /opt/mssql/bin/mssql-conf setup

• Setting up Microsoft SQL Server  
• Enter the new SQL Server system administrator password:   --Enter strong password here  
• Confirm the new SQL Server system administrator password: --Enter strong password here  
• Starting Microsoft SQL Server...  
• Enabling Microsoft SQL Server to run at boot...  
• Setup completed successfully.
• Check Mssql services status using command sudo systemctl status mssql-server

 2. Change password using sp_password 

• Login with User who having sysadmin access on the server.
•  syntax: sp_password NULL, ‘<insert_new_password_here>’, ’sa’ 
• command :

sp_password NULL, 'Mssql@12345', 'sa'

 

 3. Using GUI using Window server:​

Install new version of SSMS   -->SSMS 17.4 is the latest version of SQL Server Management Studio.

   a. Connect Linux SQL server on Windows server using SSMS.
   b. Select  the Security->Logins folder on the left side of your window; Right-click “sa” and choose properties;
 

c.Change password and confirm with complexity. 
   orignal article

MSSQL Server for Linux: Troubleshooting

Introduction:

Normally there is issue or user error see in MSSQL, we check using operating system.Dba should aware of some Linux command.

Commands:

We need to check whether SQL services is running which process id and CPU utilization and read error log.

1.check SQL error log 

pwd:-cd /var/opt/mssql/log

2.list error log file in MSSQL

# ls err* ----

errorlog errorlog.10 errorlog.12 errorlog.14 errorlog.16 errorlog.18 errorlog.2 errorlog.21 errorlog.23 errorlog.3 errorlog.5 errorlog.7 errorlog.9

errorlog.1 errorlog.11 errorlog.13 errorlog.15 errorlog.17 errorlog.19 errorlog.20 errorlog.22 errorlog.24 errorlog.4 errorlog.6 errorlog.8

3.head command

# head -50 errorlog ---> Display 50 lines from errorlog

2017-08-15 20:28:43.67 Server Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)

  Apr 13 2017 11:44:40

  Copyright (C) 2017 Microsoft Corporation. All rights reserved.

  Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

2017-08-15 20:28:43.67 Server UTC adjustment: 5:30

2017-08-15 20:28:43.67 Server (c) Microsoft Corporation.

2017-08-15 20:28:43.68 Server All rights reserved.

2017-08-15 20:28:43.68 Server Server process ID is 4120.

2017-08-15 20:28:43.68 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.

2017-08-15 20:28:43.68 Server Registry startup parameters:

  -d /var/opt/mssql/data/master.mdf

  -l /var/opt/mssql/data/mastlog.ldf

  -e /var/opt/mssql/log/errorlog

2017-08-15 20:28:43.69 Server SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

2017-08-15 20:28:43.70 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2017-08-15 20:28:43.70 Server Detected 3194 MB of RAM. This is an informational message; no user action is required.

2017-08-15 20:28:43.70 Server Using conventional memory in the memory manager.

2017-08-15 20:28:44.03 Server Buffer pool extension is already disabled. No action is necessary.

2017-08-15 20:28:44.26 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.

2017-08-15 20:28:44.26 Server Implied authentication manager initialization failed. Implied authentication will be disabled.

2017-08-15 20:28:44.28 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384'].

2017-08-15 20:28:44.34 Server The maximum number of dedicated administrator connections for this instance is '1'

2017-08-15 20:28:44.34 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2017-08-15 20:28:44.35 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2017-08-15 20:28:44.36 Server In-Memory OLTP initialized on lowend machine.

2017-08-15 20:28:44.54 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

2017-08-15 20:28:44.55 Server Query Store settings initialized with enabled = 1,

2017-08-15 20:28:44.56 spid6s Starting up database 'master'.

2017-08-15 20:28:44.56 Server Software Usage Metrics is disabled.

2017-08-15 20:28:45.13 spid6s 10 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.

2017-08-15 20:28:45.24 spid6s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.

2017-08-15 20:28:45.25 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2017-08-15 20:28:45.46 spid6s Buffer pool extension is already disabled. No action is necessary.

2017-08-15 20:28:45.47 spid6s Resource governor reconfiguration succeeded.

2017-08-15 20:28:45.47 spid6s SQL Server Audit is starting the audits. This is an informational message. No user action is required.

2017-08-15 20:28:45.47 spid6s SQL Server Audit has started the audits. This is an informational message. No user action is required.

2017-08-15 20:28:45.65 spid6s SQL Trace ID 1 was started by login "sa".

2017-08-15 20:28:45.69 spid6s Server name is 'DBA04'. This is an informational message only. No user action is required.

4.check sql server is running or not

# sudo systemctl status mssql-server

 mssql-server.service - Microsoft SQL Server Database Engine

  Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

  Active: active (running) since Tue 2017-08-15 20:28:31 IST; 1 day 12h ago

  Docs: https://docs.microsoft.com/en-us/sql/linux

 Main PID: 56656 (sqlservr)

  CGroup: /system.slice/mssql-server.service

  ├─56656 /opt/mssql/bin/sqlservr

  └─56699 /opt/mssql/bin/sqlservr

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.20 spid6s ....

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.21 spid6s ....

Aug 15 20:28:48 DBA04 sqlservr[56656]: 2017-08-15 20:28:48.78 spid9s ....

Aug 15 20:28:49 DBAT04 sqlservr[56656]: 2017-08-15 20:28:49.32 spid9s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.36 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.36 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.44 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.62 spid6s ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ...]

Hint: Some lines were ellipsized, use -l to show in full.

5.process running MSSQL

# ps -ef| grep mssql

root 48427 48368 0 08:50 pts/0 00:00:00 grep --color=auto mssql

mssql 56656   1 0 Aug15 ? 00:00:00 /opt/mssql/bin/sqlservr

mssql 56699 56656 1 Aug15 ? 00:23:26 /opt/mssql/bin/sqlservr

56699 is process id running for mssql.

6.top command

--> show information like tasks,memory,cpu and swap. 

# top

top - 09:29:24 up 119 days, 21:47, 3 users, load average: 0.03, 0.21, 0.30

Tasks: 238 total, 1 running, 235 sleeping, 2 stopped, 0 zombie

%Cpu(s): 0.4 us, 0.3 sy, 0.0 ni, 95.3 id, 3.9 wa, 0.0 hi, 0.0 si, 0.0 st

KiB Mem : 4088260 total, 73712 free, 1553332 used, 2461216 buff/cache

KiB Swap: 8191996 total, 8033108 free, 158888 used. 1353632 avail Mem

  PID USER PR NI VIRT RES SHR S %CPU %MEM   TIME+ COMMAND

42936 oracle -2 0 1488876 16912 14236 S 2.0 0.4 53:54.96 ora_vktm_magic

56699 mssql 20 0 4069136 536168 2640 S 1.3 13.1 23:50.96 sqlservr

42994 oracle 20 0 1489488 41560 38508 S 1.0 1.0 0:36.71 ora_mmnl_magic

49435 root 20 0 157840 2380 1564 R 0.7 0.1 0:00.03 top

57044 mongod 20 0 1078456 37520 2152 S 0.7 0.9 61:40.10 mongod

 3274 root 20 0 0 0 0 S 0.3 0.0 0:00.97 kworker/3:1

  1 root 20 0 191004 2920 1424 S 0.0 0.1 27:26.09 systemd

  2 root 20 0 0 0 0 S 0.0 0.0 0:00.03 kthreadd

  3 root 20 0 0 0 0 S 0.0 0.0 1:52.46 ksoftirqd/0

  5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H

  7 root rt 0 0 0 0 S 0.0 0.0 0:08.03 migration/0

  8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh

CPU utilization is highlighted in yellow color.

7. grep command

-->The grep command searches the given input files for lines containing a match or a text string. Following example where find process which is word related MSSQL.

ps -ef | grep mssql mssql    36817     1  0 Jul28 ?        00:00:00 /opt/mssql/bin/sqlservr mssql    36851 36817  1 Jul28 ?        04:46:58 /opt/mssql/bin/sqlservr root     55572 55513  0 19:54 pts/0    00:00:00 grep --color=auto mssql

8.Check mssql services packages is installed 

# ps -ef | grep mssql

  mssql    36817     1  0 Jul28 ?        00:00:00 /opt/mssql/bin/sqlservr

  mssql    36851 36817  1 Jul28 ?        04:46:58 /opt/mssql/bin/sqlservr

  root     55572 55513  0 19:54 pts/0    00:00:00 grep --color=auto mssql

9. check package information of mssql

# yum info mssql-server

   Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

 * base: centos.mirror.net.in

 * epel: kartolo.sby.datautama.net.id

 * extras: centos.excellmedia.net

 * updates: centos.excellmedia.net

Installed Packages

Name        : mssql-server

Arch        : x86_64

Version     : 14.0.900.75

Release     : 1

Size        : 870 M

Repo        : installed

From repo   : packages-microsoft-com-mssql-server

Summary     : Microsoft SQL Server Relational Database Engine

License     : Commercial

Description : The mssql-server package contains the Microsoft SQL Server Relational Database Engine.

10.  Check file system 

mount | grep "^/dev" --check file system must be XFS or EXT4. 

 

11.Check ip of server

ifconfig

SSMS 17.x is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

SSMS can be installed in the following languages:

SQL Server Management Studio 17.5:
Chinese (People's Republic of China) | Chinese (Taiwan) | English (United States) | French | German | Italian | Japanese | Korean | Portuguese (Brazil) | Russian | Spanish2

SQL Server Management Studio 17.5 Upgrade Package (upgrades 17.x to 17.5):
Chinese (People's Republic of China) | Chinese (Taiwan) | English (United States) | French | German | Italian | Japanese | Korean | Portuguese (Brazil) | Russian | Spanish