Home > Forum > Database > Problem calling the data source

Problem calling the data source
0

After installing a new development environment, I have a problem with performing an SQL query to the BPS_Config database. The environment was installed with my administrative account. From the database level, I can also see that the users of the service and the application pool (db owner) have permissions to the database. The application pool is the account indicated in the installer as the user to connect to the database. The message itself points to a SID that I don't have in my domain. Every time I reinstall the environment, I get the same message, but with a different SID. I haven't encountered such a problem before. I am installing on the BPS 2022.1.4.207 version.

Also from the Designer Studio level (connections), pointing to the database server and database name, I can't get in if I enter the username and password. Windows authentication works.

MVP

Hi,

If the Account you are trying to use in the webcon connection definition is a domain account, it won't work,
you should use a sql account, or Windows authentication . In sql managment studio will work but you have to run studio as this domain user (right click on application and run as)

In reply to: Karol Częczek

Hi,

If the Account you are trying to use in the webcon connection definition is a domain account, it won't work,
you should use a sql account, or Windows authentication . In sql managment studio will work but you have to run studio as this domain user (right click on application and run as)

OK, but the basic problem is the inability to query the BPS_Config source with the system account. For the PROD/TEST environment there is no such problem.

MVP
In reply to: Dariusz Tułacz

OK, but the basic problem is the inability to query the BPS_Config source with the system account. For the PROD/TEST environment there is no such problem.

Hi,
if it's a brand new install of a new version, it's probably related to a change that webcon has added in terms of security and system account permissions.
If you want to be able to perform a query in the context of the config database, you will not use the default connection now, you should use a defined connection to a specific database.

You can e.g. create a connection to the bps_content database and windows authenticate. and then in the place where you want to use, for example, a join to content and config (because I assume that's what it's about) use this connection and in the query enter the entire path to config [baza][dbo].[bps_config], e.g.

MVP

Hi,

I think the reason for this behavior is that 2022 R3 version uses Low privilege user to execute SQL COMMANDs and maybe that user doesn't have sufficient permissions to BPS_Content_Att database.

Please see attached screenshot of 2022.1.3.47 build's changelog.


You should try following things:

- check SQL Low privilege user by executing following query on your config database:

SELECT PRM_Name, PRM_Value FROM GlobalParameters WHERE PRM_Name='SQLLowPrivilegeUser'

- check if that user is mapped to all databases you're trying to access (https://stackoverflow.com/questions/19009488/the-server-principal-is-not-able-to-access-the-database-under-the-current-securi)

Thanks,
Raluca

In reply to: Raluca-Mirabela Lupu

Hi,

I think the reason for this behavior is that 2022 R3 version uses Low privilege user to execute SQL COMMANDs and maybe that user doesn't have sufficient permissions to BPS_Content_Att database.

Please see attached screenshot of 2022.1.3.47 build's changelog.


You should try following things:

- check SQL Low privilege user by executing following query on your config database:

SELECT PRM_Name, PRM_Value FROM GlobalParameters WHERE PRM_Name='SQLLowPrivilegeUser'

- check if that user is mapped to all databases you're trying to access (https://stackoverflow.com/questions/19009488/the-server-principal-is-not-able-to-access-the-database-under-the-current-securi)

Thanks,
Raluca

Key information for me:
For newly created installations, all queries using the Default connection and queries executed in SQL COMMAND rules will always be executed in the context of BPS_User. It is not possible to change this behavior.

New installation - DEV: bps_user
Upgrade - TEST: NULL
Upgrade - PROD: NULL

It looks like bps_user was not created in the database (Security -> Logins).
Should I create this user manually and give him permissions to databases?
Or maybe during the installation I should give the BPS_User a password and then not select the option of connecting through the application pool?

MVP
In reply to: Dariusz Tułacz

Key information for me:
For newly created installations, all queries using the Default connection and queries executed in SQL COMMAND rules will always be executed in the context of BPS_User. It is not possible to change this behavior.

New installation - DEV: bps_user
Upgrade - TEST: NULL
Upgrade - PROD: NULL

It looks like bps_user was not created in the database (Security -> Logins).
Should I create this user manually and give him permissions to databases?
Or maybe during the installation I should give the BPS_User a password and then not select the option of connecting through the application pool?

Hi Dariusz,

the bps_user is a user without a login. Therefore you can’t give him a password. Changing this would probably cause errors when WEBCON wants to use the user.

I'm copying a related error message for this situation when the bps_user is used for something, where it has no sufficient permissions, so there are more chances that other see this:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The server principal "S-1-9-3-*************************************" is not able to access the database "PIT" under the current security context.


Best regards,
Daniel

In reply to: Daniel Krüger (Cosmo Consult)

Hi Dariusz,

the bps_user is a user without a login. Therefore you can’t give him a password. Changing this would probably cause errors when WEBCON wants to use the user.

I'm copying a related error message for this situation when the bps_user is used for something, where it has no sufficient permissions, so there are more chances that other see this:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The server principal "S-1-9-3-*************************************" is not able to access the database "PIT" under the current security context.


Best regards,
Daniel

Is it correct operation of the system that this user does not have permissions to the Config database right after installation?

MVP
In reply to: Dariusz Tułacz

Is it correct operation of the system that this user does not have permissions to the Config database right after installation?

Hi Dariusz,

yes that's right.

The "bps_user" without a login exists only in one database and you "can't" access another database with it. Even if you would have the same user name in two databases it wouldn't work.
When I researched this a bit I came to a blog that this would be possible by assigning certificates or so to both users and which would be used to "allow" them the execution. I didn't go down this road.


If you are more interested in this topic you can take a look here.
https://blog.sqlauthority.com/2012/09/29/sql-server-importance-of-user-without-login/

Best regards,
Daniel

In reply to: Daniel Krüger (Cosmo Consult)

Hi Dariusz,

yes that's right.

The "bps_user" without a login exists only in one database and you "can't" access another database with it. Even if you would have the same user name in two databases it wouldn't work.
When I researched this a bit I came to a blog that this would be possible by assigning certificates or so to both users and which would be used to "allow" them the execution. I didn't go down this road.


If you are more interested in this topic you can take a look here.
https://blog.sqlauthority.com/2012/09/29/sql-server-importance-of-user-without-login/

Best regards,
Daniel

Thank you very much for all the tips.

Regards
Derek