Home > Forum > Rules, JS, SQL > Webconapps - Connecting to an Azure SQL

Webconapps - Connecting to an Azure SQL
0

MVP

Hello,

I have the following scenario:
- Webconapps
- Data Sources > Connections > MSSQL Database > Azure_SQL: I have server name, database, user and password filled. When I click Test, I get "OK" so Webcon can connect to the database.

However, when I try to create a MSSQL database Data Source and chose the above connection, I get the following error:
"Unknown error: The SELECT permission was denied on the object 'table_name', database 'database_name', schema 'dbo'..."

Using SSMS the server where the SQL is installed, I can SELECT the data from the tables.

What could be wrong?

Thank you in advance for your help,
Martin

MVP
In reply to: Maksymilian Stachowiak

First thing I'd check if you are using for sure same user on SSMS and in the data source.

Running SSMS on the server gives me a hint, that there might be an integrated authentication, when BPS gets passwords to another login/user.

The user and password are fine.

It has to do something with permissions, I just can’t figure out what.

I just noticed the same issue if using dbeaver. So I’m guessing SSMS does something different when trying to run a select query.

Any other hints?

In reply to: Martin Meze (Freelancer)

The user and password are fine.

It has to do something with permissions, I just can’t figure out what.

I just noticed the same issue if using dbeaver. So I’m guessing SSMS does something different when trying to run a select query.

Any other hints?

Martin,
could it be a firewall? Is it Azure SQL Managed Instance or just Azure SQL database? Notice that these are different products and have different specifications.
Take a look here:
https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview?view=azuresql

MVP
In reply to: Adam Hatak

Martin,
could it be a firewall? Is it Azure SQL Managed Instance or just Azure SQL database? Notice that these are different products and have different specifications.
Take a look here:
https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview?view=azuresql

Not sure if firewall could be the root cause.

Let me summarize once again + add some additional info:
- It's Microsoft SQL Azure (RTM) - 12.0.2000.8
- Using SQL authentication
- Webcon Data sources > Connections > MSSQL database > inserted all connection parameters, click Test and get the "Connection succeeded" result.
- SELECT statement works if using SSMS
- SELECT statement doesn't work if using dbeaver (from the same machine as previous bullet point)
- SELECT statement doesn't work if trying from Webcon Data Source window (using the above mentioned connection).

Any ideas?

In reply to: Martin Meze (Freelancer)

Not sure if firewall could be the root cause.

Let me summarize once again + add some additional info:
- It's Microsoft SQL Azure (RTM) - 12.0.2000.8
- Using SQL authentication
- Webcon Data sources > Connections > MSSQL database > inserted all connection parameters, click Test and get the "Connection succeeded" result.
- SELECT statement works if using SSMS
- SELECT statement doesn't work if using dbeaver (from the same machine as previous bullet point)
- SELECT statement doesn't work if trying from Webcon Data Source window (using the above mentioned connection).

Any ideas?

It for sure doesn't look like firewall/networking issue - there would be no connection at all.
Webcon shows that connection is OK - it means that the user can login - it propably doesn't verify any permissions though.

There is connection to the database, but the user somehow have no rights for select so i'd keep looking in SSMS on users and logins securables.