ODBC Mode Authentication Permission Check
There are two types of authentication method in ODBC backup mode: Trusted Authentication and MS SQL Authentication. The following procedures can help to determine:
- If the login credentials used to authenticate a MS SQL Server database backup in ODBC backup mode has the correct permissions.
- The size of the spooled database(s) in order to make sure the drive where the temporary folder is located has enough space to accommodate the spooling of the database(s) during backups.
Trusted Authentication
To verify if the login credentials for Trusted Authentication have the correct permissions to access and spool your MS SQL Server database(s) for a backup job, it is recommended to use the following command:
osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'xxx' BACKUP DATABASE
@dbname TO DISK = '%temporary_path%\%database_name.txt' WITH SKIP"
- %temporary_path% is the location of the temporary folder on the MS SQL Server backup set (e.g. E:\temp)
- ‘xxx’ is the name of the database selected for backup
Example Scenario: Trusted Authentication using Windows User Account with Sufficient Permissions
- Log in to Windows using a specific account (e.g. Administrator).
- Open the command prompt.
Use the following osql command.
Microsoft Windows [Version 10.0.14393] (c) 2016 Microsoft Corporation. All rights reserved. C:\Users\Administrator>osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'adventureworks2016' BACKUP DATABASE @dbname TO DISK ='E:\temp\adventureworks2016.bak' WITH SKIP" Processed 26240 pages for database 'adventureworks2016 ', file 'AdventureWorks2016_Data' on file 5. Processed 2 pages for database 'adventureworks2016 ', file 'AdventureWorks2016_Log' on file 5. BACKUP DATABASE successfully processed 26242 pages in 14.397 seconds (14.239 MB/sec). C:\Users\Administrator>In the example above:
@dbname = 'adventureworks2016'
'adventureworks2016' is the name of the database selected for backup
@dbname TO DISK='E:\temp\adventureworks2016.bak'
'E:\temp' is the temporary folder of the MS SQL Server backup set
If the ‘adventureworks2016’ database is successfully saved to the temporary folder (E:\Temp), then it is verified that the account has the correct permissions. To check if the database is created successfully, and to obtain the size of the spooled database(s) in order to make sure the temporary drive has enough space to accommodate the spooling of the database files during backups, run the following command.
C:\Users\Administrator>dir E:\Temp Volume in drive E has no label. Volume Serial Number is 16F4-EB51 Directory of E:\Temp 09/07/2020 11:18 AM <DIR> . 09/07/2020 11:18 AM <DIR> .. 09/07/2020 12:06 PM 1,075,228,160 adventureworks2016.bak 09/07/2020 11:18 AM 215,046,656 inventory.bak 09/04/2020 06:42 PM 4,024,832 master.bak 3 File(s) 1,429,402,815 bytes 2 Dir(s) 21,348,990,976 bytes free
Example Scneario: Trusted Authentication using Account with Incorrect Permissions
The following error message will be displayed when using a Windows login account which does not have the correct permissions to access the MS SQL database(s).
C:\Users\backup1>osql -E -Q "DECLARE @dbname char(64) SET @dbname =
'adventureworks2016' BACKUP DATABASE @dbname TO DISK
='E:\temp\adventureworks2016.bak' WITH SKIP"
[ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a
connection to SQL Server [2].
Login failed for user 'W2K16-MSSQL2K16\backup1'.
[ODBC Driver 13 for SQL Server]A network-related or instance-specific error
has occurred while establishing a connection to SQL Server. Server is not
found or not accessible. Check if instance name is correct and if SQL
Server is configured to allow remote connections. For more information see
SQL Server Books Online.
C:\Users\backup1>
In the example above, the user ‘backup1’ does not have the correct permissions. Therefore, a MS SQL Server connection cannot be established.
MS SQL Authentication
To verify if the login credentials for MS SQL Authentication have the correct permissions to access and spool your MS SQL Server database(s) for a backup job, it is recommended to use the following command:
osql -U USERNAME -P PASSWORD -Q "DECLARE @dbname char(64) SET @dbname =
'xxx' BACKUP DATABASE @dbname TO DISK =
'%temporary_path%\%database_name.txt' WITH SKIP"
- %temporary_path% is the location of the temporary folder on the MS SQL Server backup set (e.g. E:\temp)
- ‘xxx’ is the name of the database selected for backup
- USERNAME is the username of the MS SQL Server account
- PASSWORD is the password of the MS SQL Server account
Example Scenario: MS SQL Authenticatoin using Account with Sufficient Permissions
- Log in to Windows using a specific account (e.g. Administrator)
- Open the command prompt.
Use the following osql command.
Microsoft Windows [Version 10.0.14393] (c) 2016 Microsoft Corporation. All rights reserved. C:\Users\Administrator>osql -U sa -P abc123$% -Q "DECLARE @dbname char(64) SET @dbname = 'master' BACKUP DATABASE @dbname TO DISK = 'E:\temp\master.bak' WITH SKIP" Processed 480 pages for database 'master ', file 'master' on file 2. Processed 3 pages for database 'master ', file 'mastlog' on file 2. BACKUP DATABASE successfully processed 483 pages in 0.785 seconds (4.802 MB/sec). C:\Users\Administrator>In the example above:
osql -U sa
'sa' is the default username of the MS SQL Server account
-P abc123$%
'abc123$%' is the password of the MS SQL Server account
@dbname = 'master'
'master' is the name of the database selected for backup
@dbname to DISK = 'E:\temp\master.bak'
'E:\temp' is the temporary folder of the MS SQL Server backup set
If the “master” database is successfully saved to the temporary folder (E:\Temp), then it is verified that the account has the correct permissions. To check if the database is created successfully, and to obtain the size of the spooled database(s) in order to make sure the temporary drive has enough space to accommodate the spooling of the database files during backups, run the following command.
C:\Users\Administrator>dir E:\Temp Volume in drive E has no label. Volume Serial Number is 16F4-EB51 Directory of E:\Temp 09/07/2020 11:18 AM <DIR> . 09/07/2020 11:18 AM <DIR> .. 09/07/2020 12:06 PM 1,075,228,160 adventureworks2016.bak 09/07/2020 11:18 AM 215,046,656 inventory.bak 09/04/2020 06:42 PM 4,024,832 master.bak 3 File(s) 1,429,402,815 bytes 2 Dir(s) 21,348,990,976 bytes free C:\Users\Administrator>
Example Scenario: MS SQL Authentication using Account with Incorrect Permissions
The following error message will be displayed when using a MS SQL account which does not have the correct permissions to access the MS SQL database(s).
C:\Users\Administrator>osql -U mssql1 -P abc123$% -Q "DECLARE @dbname
char(64) SET @dbname = 'master' BACKUP DATABASE @dbname TO DISK =
'E:\temp\master.bak' WITH SKIP"
[ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a
connection to SQL Server [2].
Login failed for user 'mssql1'.
[ODBC Driver 13 for SQL Server]A network-related or instance-specific
error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is
correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.
C:\Users\Administrator>
On the example above, ‘mssql1’ is the username of the MS SQL Server account which does not have the correct permissions. Therefore, a MS SQL Server connection cannot be established.
How-To