bwoodfield
Programmer
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?
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?
