How to Generate Blob SAS URL for a excel file stored in Blob Container

Question

I am trying to generate Blob SAS URL for a excel file to read its data in Data frames. I am using below python code which throws an error while passing the URL value to read_excel function "HTTPError: Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature."

Code :

    from azure.storage.blob import generate_blob_sas
    from azure.storage.blob import BlobServiceClient, ResourceTypes, AccountSasPermissions
    from datetime import datetime, timedelta,date
    import pandas as pd
    
    blob_name=<Blobname>
    account_name=<accountname>
    account_key=<accountkey>
    container_name=<blobname>
    
    sas_blob = generate_blob_sas(account_name=account_name, 
                              container_name=container_name,
                                blob_name=blob_name,
                                account_key=account_key,
                                resource_types=ResourceTypes(object=True),
                                permission=AccountSasPermissions(read=True),
                               expiry=datetime.utcnow() + timedelta(hours=1))
    
    blob = generate_blob_sas(account_name,account_key, container_name, blob_name,sas_blob)
    blob_service_client = BlobServiceClient(account_url="https://<account_name>.blob.core.windows.net", credential=sas_blob)
    url = 'https://'+account_name+'.blob.core.windows.net/'+container_name+'/'+blob_name+'?'+sas_blob
    print(url)
    df=pd.read_excel(url, sheet_name='test',usecols=(cols),header=6)

Error Failed C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\azure\storage\blob\baseblobservice.py:1009: SyntaxWarning: "is not" with a literal. Did you mean "!="? if lease_duration is not -1 and \C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\azure\storage\blob\baseblobservice.py:2660: SyntaxWarning: "is not" with a literal. Did you mean "!="? if lease_duration is not -1 and \C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\azure\storage\common_connection.py:82: SyntaxWarning: "is" with a literal. Did you mean "=="? self.protocol = self.protocol if parsed_url.scheme is '' else parsed_url.schemeTraceback (most recent call last): File "C:\Temp\rid04ztb.tl0\005b3440-f226-432b-b554-d625411fdb58", line 26, in df=pd.read_excel(url, sheet_name='test',usecols=(cols),header=6) File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\util_decorators.py", line 299, in wrapper return func(*args, **kwargs) File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\excel_base.py", line 336, in read_excel io = ExcelFile(io, storage_options=storage_options, engine=engine) File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\excel_base.py", line 1071, in init ext = inspect_excel_format( File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\excel_base.py", line 949, in inspect_excel_format with get_handle( File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\common.py", line 558, in get_handle ioargs = _get_filepath_or_buffer( File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\common.py", line 289, in _get_filepath_or_buffer req = urlopen(filepath_or_buffer) File "C:\WPy64-3800\python-3.8.0.amd64\lib\site-packages\pandas\io\common.py", line 195, in urlopen return urllib.request.urlopen(*args, **kwargs) File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 222, in urlopen return opener.open(url, data, timeout) File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 531, in open response = meth(req, response) File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 640, in http_response response = self.parent.error( File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 569, in error return self._call_chain(*args) File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 502, in _call_chain result = func(*args) File "C:\WPy64-3800\python-3.8.0.amd64\lib\urllib\request.py", line 649, in http_error_default raise HTTPError(req.full_url, code, msg, hdrs, fp)urllib.error.HTTPError: HTTP Error 403: Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature.

Any help appreciated. Thanks in advance.

Gaurav Mantri Gaurav Mantri · Accepted Answer · 2021-06-16T05:45:32

I believe you're getting this error is because you're mixing a service SAS with account SAS. You don't need resource_types in your generate_blob_sas method and also the permission type should be BlobSasPermissions.

Please try the following code:

from azure.storage.blob import generate_blob_sas
from azure.storage.blob import BlobServiceClient, ResourceTypes, BlobSasPermissions
from datetime import datetime, timedelta,date
import pandas as pd

blob_name=<Blobname>
account_name=<accountname>
account_key=<accountkey>
container_name=<blobname>

sas_blob = generate_blob_sas(account_name=account_name, 
                            container_name=container_name,
                            blob_name=blob_name,
                            account_key=account_key,
                            permission=BlobSasPermissions(read=True),
                            expiry=datetime.utcnow() + timedelta(hours=1))

How to Generate Blob SAS URL for a excel file stored in Blob Container

1 Answers