Advertisements
Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Error – Cannot open backup device ‘https://xyz.blob.core.windows.net/xyz/xyz.bak’. Operating system error 50(The request is not supported.).

February 6, 2018 1 comment

 
Today I got an email from someone regarding an issue he was facing. He was trying to Backup SQL Server database to URL i.e. the Azure Blob Storage, by issuing below Backup command:

USE [master]
GO

BACKUP DATABASE [ManTest]
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak'
 WITH 
	COMPRESSION  
    ,STATS = 5;  
GO

And he was getting following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
‘https ://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak’.
Operating system error 50(The request is not supported.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

By checking the above error, you can make out that we are not able to connect to the Azure Storage resource due to some access issues. I checked online and found that you need to create Credentials on SQL Server from where you want to access the Azure Storage resource, and there are few methods for the same:
 

–> Method #1: Using storage account identity and Access Key

Azure Access Keys authenticates your applications when making requests to the Azure storage account. You will have to create a Credential on SQL Server end by providing the Access Key from your Azure Storage account, check below:

USE [master]
GO

CREATE CREDENTIAL [DBBackupCred] 
WITH IDENTITY = 'mantestaz'  -- Storage Account Name
,SECRET = 'xyx35HWTOnkDpiHkNWayz2Gsw6Figyxyx=='; -- Access key
GO

– Now you can fire the BACKUP command with the additional WITH CREDENTIAL option so that the Access can be authenticated for storing and writing backups on Azure Blob Container:

USE [master]
GO

BACKUP DATABASE [ManTest]  
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak' 
     WITH CREDENTIAL = 'DBBackupCred'   
    ,COMPRESSION  
    ,STATS = 5;  
GO

38 percent processed.
77 percent processed.
99 percent processed.
Processed 328 pages for database ‘ManTest’, file ‘ManTest’ on file 1.
100 percent processed.
Processed 3 pages for database ‘ManTest’, file ‘ManTest_log’ on file 1.
BACKUP DATABASE successfully processed 331 pages in 15.792 seconds (0.163 MB/sec).

… and its done successfully.
 

–> Method #2: Using Shared Access Signature (SAS)

A Shared Access Signature (SAS) is a URI that grants restricted access rights to Azure Storage resources. You can provide a shared access signature to clients who should not be trusted with your storage account key but whom you wish to delegate access to certain storage account resources. By distributing a shared access signature URI to these clients, you grant them access to a resource for a specified period of time.

Here is the syntax to create SAS Credential:

USE [master]
GO

CREATE CREDENTIAL 
	[https://<storageaccountname>.blob.core.windows.net/<containername>] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = '<SAS_TOKEN>'; 

Advertisements

SQL Trivia – SQL Server Code names

January 25, 2018 Leave a comment

 
Every time the SQL Server Product team starts working with their new version of SQL Server they give a Code Name to it, and before the final name is announced (which goes with the year name) the Code name gets popular among them, developers, users and the whole SQL community. But sometimes its very tricky and confusing to keep track of the Code Names for the older versions.

So, here is a list of SQL Server various versions I’ve compiled which lists the SQL Server release Final Name with Code Name, and other details like Year released, Version number and OS supported:

Year Final Name Version Code Name OS Support
2017 SQL Server 2017  14.0 Helsinki Win & Linux
2016 SQL Server 2016  13.0 Windows
2014 SQL Server 2014  12.0 Hekaton Windows
2012 SQL Server 2012  11.0 Denali Windows
2010 SQL Server 2008 R2  10.5 Kilimanjaro Windows
2010 SQL Azure DB  10.25 Cloud DB Azure
2008 SQL Server 2008  10.0 Katmai Windows
2005 SQL Server Analysis Services  – Picasso Windows
2005 SQL Server 2005  9.0 Yukon Windows
2003 SQL Server Reporting Services  – Rosetta Windows
2003 SQL Server 2000 x64 (64 bit)  8.0 Liberty Windows
2000 SQL Server 2000 x86 (32 bit)  8.0 Shiloh Windows
1999 SQL Server 7.0 OLAP Services  – Plato Windows
1998 SQL Server 7.0  7.0 Sphinx Windows
1996 SQL Server 6.5  6.5 Hydra Windows
1995 SQL Server 6.0  6.0 SQL95 Windows
1993 SQL Server 4.21  4.21 Windows
1992 SQL Server 4.2  4.2 OS/2
1991 SQL Server 1.1 (16 bit)  1.1 Pietro OS/2
1990 SQL Server 1.0 (16 bit)  1.0 Filipi OS/2

 

You can also check my post on [SQL and its history] !!!


SQL Error – SQL Server Installation fails | Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed)…

January 9, 2018 1 comment

 
Few days back I got this query from a developer who was trying to install SQL Server, it was not getting installed and was giving below error:

Microsoft.SqlServer.Configuration.Sco.DirectoryAttributesMissmatch: Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed) , included attributes (0), excluded attributes (Compressed, Encrypted).


 

–> RCA: As the above error message mentions that the Directory is Compressed, it gives you an idea that the SQL Server installation is not supported on compressed drives. So, if you are installing SQL Server on a drive which has compression enabled it will give you this error.
 

–> Fix: You have to check the Drive properties and see if Compression is enabled, like shown below and un-compress it by un-checking the option.


Seeking response on: LDAP Authentication with SQL Server 2017 on Linux

January 8, 2018 1 comment

 
Few days back I got an email from one of the reader of this blog “Amit Bhatt”. As I haven’t worked much with the AD/LDAP stuff, thus I thought to throw this question to you guys thru this blog post. I also feel this may also help other Developers/DBAs hunting for similar stuff.
 

Here he goes:

We have installed SQL Server 2017 on Linux server. I am able to connect SQL Server locally as well remotely but with local user access.

I have my AD account created and have AD server information. Can you please help me how can I connect using my AD account to SQL Server 2017 on Linux remotely?

I heard something about LDAP Authentication. Is it possible to use this concept without adding Linux server to AD domain? Our security team does not allow to add Linux server in Active Directory group.

I am stucked here since last many days, requesting assistance on urgent basis.

 

Please provide your suggestion on the comment section below.
 

–> Responses:

1. To support AD Authentication, SQL depends on SSSD, SPN and a keytab file which have the required tokens to talk to AD. Without these in place SQL cannot talk to AD which is necessary for AD authentication.
Ref: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

2. If you are looking to login to SQL Server on linux with Windows authentication the linux server should be joined to the domain. If the server cannot be added to domain then SQL authentication is the way to go. The below link has more details to configure windows authentication, https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication


 

–> Videos on Linux:

1. Create a Linux VM on Azure

2. Install SQL Server on Linux Azure VM

3. Connect SQL Server on Linux VM from SSMS


Download & Install SQL Server Reporting Services (SSRS) 2017 (decoupled from SQL Server engine setup)

January 7, 2018 3 comments

 
In one of my [previous blog] post related to SQL Server 2017, I mentioned regarding SQL Server Reporting Services (SSRS) that it will no longer be installed from the main feature tree of SQL Server engine setup, just like SSMS.

In SQL Server 2016, the SSMS Setup was taken away from the SQL Setup
 

As per Microsoft, this is basically to support the move to make a universal version of SSRS that may ship more frequently that the whole SQL Server v-next.

 
So, now on wards after installing SQL Server 2017 you need to install SSRS separately, just like SSMS.
 

–> Till SQL Server 2016 you have an option of choosing “Reporting Services” in the Feature selection tree, but with SQL Server 2017 and on wards this option is taken out, can be seen in the pic below:


 
–> Now to download SSMS 2016 you can either visit the Microsoft [download page]

– Or –

Install directly via the “Installation Center” as shown below. This will install SSMS directly online.

So, once you take appropriate action above to download SSRS 2017, the Installation kicks off like this:


 

Check my blog posts on most of the new features released in SQL Server 2017.