VBA Connection string from Excel to Azure Serverless SQL Pool using Microsoft Account (Azure AD)

Question

VBA Connection string from Excel to Azure Serverless SQL Pool using Microsoft Account (Azure AD)

Renato de Melo 125

I need to connect Excel to Azure Serveless SQL Pool (SSQLP). Once a user opens the spreadsheet, I will refresh data from SSQLP considering the authenticated user. What is the format of the connection string, considering the Excel/Windows authenticated user for this scenario? I will not provide password, neither user id in the connection string. It needs to use single-sign-on.

I appreciate any help.

Regards,

Renato de Melo

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-08T20:55:25.7666667+00:00

Hello

Renato de Melo,

You can try connect to synapse serverless SQL pool via the Get data -->From Azure--> from Azure synapse analytics.

and then using your Azure AD credentials(SSO) to log on to the serverless SQL pool.

Please see the below screenshots for your reference.

I hope this helps.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-12T19:54:54.98+00:00

Hello Renato de Melo,

I am checking to see if you have any further questions here.
Renato de Melo 125 Reputation points

2024-03-15T19:07:54.3533333+00:00

@Bhargava-MSFT I did that already before I posted the question. The point is, the data imported via Get Data wizard, is stored inside the spreadsheet. If a user sends the spreadsheet to another user, the other user will see the data.

In my use case, data has RLS rules (row-level-security). Thus even though users share the spreadsheet, the user which receives a spreadsheet copy, might not be authorized to see the data stored in the spreadsheet.

To void this, I tried to apply VBA to force the login and I did not find any valid connection string from VBA to Synapse with used the logged user. That was my question.

While I was waiting for a reply, I found an option to disable data stored inside spreadsheet and to force user login: "Refresh data when opening the file", "Remove data from external data range before saving the workbook".

This options always force the user to login/refresh data, but the price is, every time the user opens the spreadsheet, it needs to wait until data is retrieve from Synapse.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-22T20:23:12.9033333+00:00

I am checking to see if you got a chance to look into my above response.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-26T15:07:19.33+00:00

Hello Renato de Melo,

I am checking to see if you had a chance to look into my above response.

1 answer

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-08T20:55:25.7666667+00:00

Hello

Renato de Melo,

You can try connect to synapse serverless SQL pool via the Get data -->From Azure--> from Azure synapse analytics.

and then using your Azure AD credentials(SSO) to log on to the serverless SQL pool.

Please see the below screenshots for your reference.

I hope this helps.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-12T19:54:54.98+00:00

Hello Renato de Melo,

I am checking to see if you have any further questions here.
Renato de Melo 125 Reputation points

2024-03-15T19:07:54.3533333+00:00

@Bhargava-MSFT I did that already before I posted the question. The point is, the data imported via Get Data wizard, is stored inside the spreadsheet. If a user sends the spreadsheet to another user, the other user will see the data.

In my use case, data has RLS rules (row-level-security). Thus even though users share the spreadsheet, the user which receives a spreadsheet copy, might not be authorized to see the data stored in the spreadsheet.

To void this, I tried to apply VBA to force the login and I did not find any valid connection string from VBA to Synapse with used the logged user. That was my question.

While I was waiting for a reply, I found an option to disable data stored inside spreadsheet and to force user login: "Refresh data when opening the file", "Remove data from external data range before saving the workbook".

This options always force the user to login/refresh data, but the price is, every time the user opens the spreadsheet, it needs to wait until data is retrieve from Synapse.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-22T20:23:12.9033333+00:00

I am checking to see if you got a chance to look into my above response.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-03-26T15:07:19.33+00:00

Hello Renato de Melo,

I am checking to see if you had a chance to look into my above response.

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Not sure if you have seen the below thread

https://stackoverflow.com/questions/70686239/vba-connect-to-dbname-ondemand-sql-azuresynapse-net

You can try the below connection string format.

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryInteractive;User ID=myUserName;Use Encryption for Data=true;

Share via

VBA Connection string from Excel to Azure Serverless SQL Pool using Microsoft Account (Azure AD)

1 answer

Your answer