1. Jeinutheen Mohamed
  2. PowerBuilder
  3. Wednesday, 5 June 2019 16:40 PM UTC

Hello all,

I'm a longtime (more than 22+ years) PowerBuilder developer. We have upgraded to PowerBuilder 2017 R2 through Appeon.

I have a question about the support for “Impersonation” using a different Windows AD System account after logging into the system. Right now we use a system account with a password to login (using MS SQL Server Native Client). Effectively, the individual users won’t have any access to any database objects. Only the system account will have access. Now, our SQL admins are implementing some changes in the process & wanted to stop using the SQL accounts (with user id & password). So, they wanted us to login to the system using Windows Authentication & then use “Impersonation” using a different Windows AD System account. This way they don’t need to give data objects access to individual users & can stop using SQL User accounts.

All the non-PB .net applications are already migrated to work this way. But, we couldn’t do that in our applications, as we couldn’t find a way to make this works with PowerBuilder 2017 R2. Is this supported by PowerBuilder?

Please note that if this is not possible, this could be the end of PowerBuilder development in our company (we’re using PowerBuilder since version 4 – since 1996). I sincerely hope for a resolution for this issue.

Thanks much for your help & support.

With respects,

Jein.

Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 11:11 AM UTC
  2. PowerBuilder
  3. # 1

Hey Jein,

Impersonation using PB app connected to MS SQL Server is straightforward.

Assume:

  • Some AD account (EX: ID = MyAppUser) has required DB access 
  • Your AD account only has access to
    • Log on to DB server - and
    • Perform EXECUTE AS USER='MyAppUser'

Now, this is the app logic you need in your PB client app:

// Set up SQLCA for Wndows Authentication
SQLCA.DBMS = ...
SQLCA.DBParms = ...

// Connect to database
CONNECT USING SQLCA;
if SQLCA.SQLCode <> 0 then throw create ex_DBConnectFailed

// Impersonate as_appUser (argument to function)
string ls_sql
ls_sql = "EXECUTE AS USER = N'" + as_appUser + "'"
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
if SQLCA.SQLCode <> 0 then throw create ex_DBConnectFailed

Note: Code throws exceptions when DB connect or impersonation fails. You may want to use return values but the main logic is the same: CONNECT; EXECUTE AS USER.

Same type of logic applies if end-users have different roles that map to different AD accounts each having different access rights granted like read-only users, call center worker, manager, etc.

So your AD group may define which app user you can EXECUTE AS. Code above also suits such scenario.

HTH /Michael

Comment
  1. Michael Kramer
  2. Thursday, 6 June 2019 13:23 PM UTC
N makes the text NVARCHAR => To make sure you can use European special characters.
  1. Helpful
  1. Jeinutheen Mohamed
  2. Thursday, 6 June 2019 16:07 PM UTC
Thanks much Michael for the response. While that sounds interesting, how does it work for datawindows. We have too many of these datawindows with some of them using stright SQL statements & some using Stored Procedures, some are updateable, etc.,



Also I was thinking there would be some easier way to just change the impersonation right after the Connect (as opposed to change the whole application (our existing apps are too big) in so many places. We used to use "sp_setapprole", then it was also depreciated as it invloves user id & password and admins wanted to use only Windows Authentication.
  1. Helpful
  1. Michael Kramer
  2. Thursday, 6 June 2019 16:48 PM UTC
Login to app only involves one EXECUTE AS USER immediately after CONNECT USING SQLCA. You can still obtain identity of the end-user if needed for logging purposes.

SELECT ORIGINAL_LOGIN( ) [End-user], USER_NAME( ) [Impersonated-user]

I don't need to modify any DataWindows - as long as the impersonated user uses the right default schema. If I still misunderstand, please give example of SQL you need to change, like current DW SELECT = SELECT friendID, friendName, nickName FROM MyFriends.



  1. Helpful
There are no comments made yet.
Jeinutheen Mohamed Accepted Answer Pending Moderation
  1. Thursday, 6 June 2019 14:09 PM UTC
  2. PowerBuilder
  3. # 2

Thanks much Michael for the response. While that sounds interesting, how does it work for datawindows. We have too many of these datawindows with some of them using stright SQL statements & some using Stored Procedures, some are updateable, etc.,

Also I was thinking there would be some easier way to just change the impersonation right after the Connect (as opposed to change the whole application (our existing apps are too big) in so many places. We used to use "sp_setapprole", then it was also depreciated as it invloves user id & password and admins wanted to use only Windows Authentication.

Comment
There are no comments made yet.
Kim Berghall Accepted Answer Pending Moderation
  1. Monday, 10 June 2019 19:26 PM UTC
  2. PowerBuilder
  3. # 3

We are interested in a similar concept to control permissions. We currently do the same thing but basically login with a full access user id (however the users don't know what that user id is and/or the password.

The impersonator concept would work for this as well, but I assume there is no way to prevent the user for executing the impersonator command outside the application? So my question then would be how do you connect to MS SQL Server using a domain account? I.e. you are already logged into the PC/network with your own ID, but the app needs to login with a different domain account. Some how it needs to be able to accept a password in this process.

Thanks,

Kim

Comment
  1. Michael Kramer
  2. Tuesday, 11 June 2019 16:55 PM UTC
Hi Kim,

There are several knobs you can turn to increase security and make breaching it harder.

Using trusted connection (= no passwords) -- Consequence: Windows Account becomes original DB server login.

You can revoke all rights except for GRANT EXECUTE ON sp_my_login;

That SP could have encrypted source so no user can extract its content.

That SP could change database, impersonate, and more to disguise what is actually available.

You can disallow queries like SELECT DB_NAME(), USER_NAME()

You can disallow execute procedures like EXEC sp_table_privilege @table_name = '%'.

You can encrypt the connection to secure data in transit.

I would prefer going this direction instead of handling passwords.

HTH /Michael
  1. Helpful
  1. Kim Berghall
  2. Tuesday, 11 June 2019 17:57 PM UTC
Yes we are aware of all of those, the problem is that we don't want to use SQL Server permissions to manage security. Instead we use our own application security system (i.e. window, report, etc. security). Thus we want the user's SQL permission to be very broad (basically full access); but we don't want the use to login to the DB with a 3rd party toll like SQL Management Studio, MS Access, MS Excel etc. Therefor we have a "single user" login mechanism. The user logs in with their own network ID (Which only has access to login to SQL Server); then we switch to a for the user unknown and encrypted user ID and password. But we would like to replace that SQL Login with a Windows domain login ID, just so we can make the SQL Server work in a Windows Authenticated mode only.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.