SSMS unable to execute openrowset query

Question

SSMS unable to execute openrowset query

Guruprasad Kulkarni 60

Hi Team,

I am unable to execute below query in SSMS due to error. However, I can execute same query in synapse studio without any issue. Please advice.

Screenshot 2023-12-24 130851

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-12-28T23:41:36.94+00:00

Hello Guruprasad Kulkarni,

As per the error message "File cannot be opened because it does not exist or it is used by another process', It seems like you are not using the same identity to query the data from synapse studio and SSMS.

If you are using same identity (Microsoft Entra identity), this problem usually happens because your Microsoft Entra identity doesn't have rights to access the file or because a firewall is blocking access to the file.

Please follow this troubleshooting document.

<copied from the document>

To resolve this issue, you must have proper rights to access the file. The easiest way is to grant yourself a Storage Blob Data Contributor role on the storage account you're trying to query.

Also, please check your firewall settings, and check the Troubleshooting guide.

I hope this helps. Please let me know if you have any further questions.
Olaf Helper 47,436 Reputation points

2023-12-29T07:39:25.1233333+00:00

SSMS is just a tool sending T-SQL queries to the server; has nothing to do with the error.

You should use a fully name address URL of the parquet file, see

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files

Accepted answer

1 additional answer

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-12-28T23:41:36.94+00:00

Hello Guruprasad Kulkarni,

As per the error message "File cannot be opened because it does not exist or it is used by another process', It seems like you are not using the same identity to query the data from synapse studio and SSMS.

If you are using same identity (Microsoft Entra identity), this problem usually happens because your Microsoft Entra identity doesn't have rights to access the file or because a firewall is blocking access to the file.

Please follow this troubleshooting document.

<copied from the document>

To resolve this issue, you must have proper rights to access the file. The easiest way is to grant yourself a Storage Blob Data Contributor role on the storage account you're trying to query.

Also, please check your firewall settings, and check the Troubleshooting guide.

I hope this helps. Please let me know if you have any further questions.
Olaf Helper 47,436 Reputation points

2023-12-29T07:39:25.1233333+00:00

SSMS is just a tool sending T-SQL queries to the server; has nothing to do with the error.

You should use a fully name address URL of the parquet file, see

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files

Answer 1

Hello Guruprasad Kulkarni,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept" the answer.

Issue: Unable to execute openrowset query from SSMS, the same query executes fine from synapse studio

Unable to execute below query in SSMS. However, the same query in synapse studio executes fine without any issue.

SELECT

Top l00 *

from openrowset (bulk '/raw/parquet/log.parquet',

data_source ='xxx',

format = 'parquet') as result

Error Message:

file '/raw/parquet/log.parquet' cannot be opened because it doesnot exist or it is used by another process

Solution:

The identity used here is different when compared to synapse studio(Microsoft entra identity) and SSMS(SQL login). The Entra ID has 'storage blob data contributor' role.

This was resolved by using the same email-id(Entra ID) while logging into SSMS.

Authentication: Azure active directory- Universal with MFA

User name: Microsoft Entra Identity

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.
Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.

Answer 2

Guruprasad Kulkarni 60

Actually, issue is, I have logged in synapse with email which has 'storage blob data contributor' role. However, in SSMS I have logged in using SQL authentication. Hence, I could access tables in database but not the file in ADLS. Hence, I have set that email as admin in Microsoft Entra and use the same email-id while logging into SSMS. I choose the option 'Microsoft Entra MFA' in SSMS which solves the problem.

Share via

SSMS unable to execute openrowset query

1 additional answer

Your answer