INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

PowerBuilder 12.1 and calling SQL Server approles

PowerBuilder 12.1 and calling SQL Server approles

(OP)
We have been updating our PowerBuilder application to utilize the SQL Server application roles. During development we have run into two issues:

The first is the translation of a Varbinary to BLOB from SQL Server to PowerBuilder. When calling the stored procedure that sets the application role, the procedure returns a Varbinary(8000) through an OUTPUT parameter,
which is used as a key to unset the role when done with the connection. In PowerBuilder a Varbinary maps to a BLOB object. For some reason when that BLOB is brought into the application and then sent back into SQL Server later, it is no longer recognized and an error that the approle has not been set or does not exist. No modification occurs, it is only stored. We've complete tests outside of PowerBuilder and the set app role/unset app role works as expected.

The second issue is unique to the use of SQL Server 2014. To get around the issue with the Varbinary->Blob issue we are storing the varbinary key directly on the database without bringing it back into PowerBuilder. Here is the code we are executing:

***for simplicity sake I removed the code that validates the SQL return codes and error reporting.

ls_sql = "SET IMPLICIT_TRANSACTIONS OFF"
EXECUTE IMMEDIATE :ls_sql USING atrTrans;

ls_sql = "SET ANSI_PADDING ON"
EXECUTE IMMEDIATE :ls_sql USING atrTrans ;

ls_sql = "create table "+atrTrans.isTempTableName+"( id int identity( 1, 1) not null, cookie varbinary(8000) not null ) "
EXECUTE IMMEDIATE :ls_sql USING atrTrans ;

ls_sql = "declare @cookie1 varbinary( 8000), @password1 sysname = '" + ls_appRollPass + "' exec sp_setapprole @rolename = '"+ ls_AppRoleName +"', @password = @password1, @fCreateCookie = 1, @cookie = @cookie1 output insert "+atrTrans.isTempTableName+"( cookie) values( @cookie1) "
EXECUTE IMMEDIATE :ls_sql USING atrTrans ;

We're creating a temp table, named for the connection, then calling the sp_setapprole and inserting the varbinary cookie into that temp table. This code WORKS in SQL Server 2012 without issue, however in SQL Server 2014 the call to the stored procedure returns an error that the approle does not exist or the password is incorrect.

We have retrieved all the SQL calls to the database by debugging through the application during the connection set-up, called the SQL through SSMS and verified that it works. We have also run the SQL Server profile trace to view the calls on the server, and can see the temp table being created, the call to the stored procedure and the statement inside the stored procedure being called up to the point that the app role is set where it fails (setuser @rolename, @password, @encrStyle, @cookie). Additionally we have non-PowerBuilder applications calling the approle in SQL Server 2014, with the same user/password and it's working correctly.


Has anyone worked with the SQL Server Application Roles before and gotten the return cookie working, OR, have experience with SQL Server 2014 and would know WHY the call to set the approle would be failing?

RE: PowerBuilder 12.1 and calling SQL Server approles

PB version? What driver for the connection?

Matt

"Nature forges everything on the anvil of time"
www.anvil-of-time.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close