SQL Server 2005 - PB 10.5.1 with Application Role

Hi,

I am looking for a good example of Application Role within PB.

The purpose is to use as logon screen the Windows Authentication mode where 
the user has to reenter his password and then use an application role in the 
application.

In such case, the user would not be authorized to use Query Analyser or 
Access to get in the database.

Thanks,

Claudy 


0
C
6/20/2007 3:29:14 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

1 Replies
456 Views

Similar Articles

[PageSpeed] 37

Here is a quick example that I cut and pasted from an existing application. 
There are some issues when using application role with PB and SQL Server.

There is an issue with connecting again (same application or different 
application) if you do not clean up the connection prior to disconnect.  The 
next use of the connection from the Windows Pool will fail.  Below is two 
approaches:

1)    You will need to disable connection pooling
        Method 1: add  ",ServiceComponents='DBPROPVAL_OS_ENABLEALL & ~ 
DBPROPVAL_OS_RESOURCEPOOLING'" to dbparm
        Method 2: add ";OLE DB Services = -2" to .udl file referenced in 
dbparm
2)    Use some method to save the cookie value returned from sp_approle 
(2005 version)



-- code after connect
EXECUTE IMMEDIATE "SET ANSI_PADDING OFF" USING l_SQLCA ;
EXECUTE IMMEDIATE "SET ANSI_WARNINGS OFF" USING l_SQLCA ;
EXECUTE IMMEDIATE "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" USING 
l_SQLCA ;
EXECUTE IMMEDIATE "SET CURSOR_CLOSE_ON_COMMIT OFF" USING l_SQLCA ;
EXECUTE IMMEDIATE "SET NOCOUNT ON" USING l_SQLCA ;
l_SQLCA.AutoCommit = FALSE

--- set approle (gst_var.ms_version is the Microsoft SQL Server version 
8.xxx of SQL 2000, 9.xx for SQL2005)
global type f_setapprole from function_object
end type

forward prototypes
global function long f_setapprole (ref transaction l_sqlca)
end prototypes

global function long f_setapprole (ref transaction l_sqlca);long i, spid

commitall( l_sqlca )

EXECUTE IMMEDIATE "SET IMPLICIT_TRANSACTIONS OFF" USING l_SQLCA ;
EXECUTE IMMEDIATE "SET ANSI_PADDING ON" using l_sqlca ;
l_SQLCA.AutoCommit = TRUE

commit using l_sqlca ;
rollback using l_sqlca ;

if mid( gst_var.ms_version, 1, 1 ) <> "9" THEN
 EXECUTE Immediate "exec sp_setapprole @rolename = ~'MyRoleName~', @password 
= ~'XXXXXXX~'" using l_sqlca ;
else
// This should always fail ---
 execute immediate "drop table #tmp_approle" using l_sqlca ;

 execute immediate "create table #tmp_approle( id int identity( 1, 1) not 
null, cookie varbinary(8000) not null ) " using l_sqlca ;
 f_check( l_sqlca, "create table" )
 commit using l_sqlca ;
 execute immediate "declare @cookie1 varbinary( 8000)  exec sp_setapprole 
@rolename = ~'MedTeam~', @password = ~'_#TRELLIX#_~',  @fCreateCookie = 1, 
@cookie = @cookie1 output insert #tmp_approle( cookie) values( @cookie1) " 
using l_sqlca ;
end if

if f_check( l_SQLCA, "f_setapprole: sp_setapprole for connection failed "  ) 
<> 0 THEN
 Halt Close
end if

// execute immediate "insert tlc_sav( spid, id, cookie ) select @@spid, id, 
cookie from #tmp_approle" using l_sqlca ;

l_SQLCA.AutoCommit = FALSE
EXECUTE IMMEDIATE "SET IMPLICIT_TRANSACTIONS ON" USING l_SQLCA ;
EXECUTE IMMEDIATE "SET ANSI_PADDING OFF" using l_sqlca ;
commitall( l_sqlca )

return 0
end function
--- disconnect logic
global type f_disconnect from function_object
end type

forward prototypes
global subroutine f_disconnect (ref transaction l_sqlca)
end prototypes

global subroutine f_disconnect (ref transaction l_sqlca);
execute immediate "declare @cnt int ; select @cnt = count(*) from 
#tmp_approle" using l_sqlca ;
if l_sqlca.sqlcode = 0 THEN
  commitall( l_sqlca )
  l_sqlca.autocommit = true
  commit using l_sqlca ;
  EXECUTE IMMEDIATE "SET IMPLICIT_TRANSACTIONS OFF" USING l_SQLCA ;
  commitall( l_sqlca )
  execute immediate "declare @cookie1 varbinary(8000)  select @cookie1 = 
cookie from #tmp_approle where id = 1  if datalength( @cookie1) > 5 exec 
sp_unsetapprole @cookie = @cookie1 " using l_sqlca ;
  if gst_var.user_name = 'tcruse' then f_check( l_sqlca, "f_disconnect: 
sp_unsetapprole failed" )
  execute immediate "drop table #tmp_approle" using l_sqlca ;
end if

commitall( l_sqlca )

disconnect using l_sqlca ;
return
end subroutine






<C> wrote in message news:467947ca$1@forums-1-dub...
> Hi,
>
> I am looking for a good example of Application Role within PB.
>
> The purpose is to use as logon screen the Windows Authentication mode 
> where the user has to reenter his password and then use an application 
> role in the application.
>
> In such case, the user would not be authorized to use Query Analyser or 
> Access to get in the database.
>
> Thanks,
>
> Claudy
> 


0
Tyler
6/28/2007 9:55:04 AM
Reply:

Similar Artilces:

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...

Restoring a SQL Server Express 2005 Database to SQL Server Standard 2005
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon  I suppose that no. Limitations are connected to SQL engine not to database itself. But probably if your database will grow up connected to SQL Standard and you will try to move it back to SQL express you will be in trouble.  ThanksJPazgier...

How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4?
Hi: How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4? It seems that the special connection inferface for "MSS Microsoft SQL Server" is canceled in PowerBuilder 10.5. I do not want to via ODBC. Thanks! Wu; No, MSS driver (actually Microsoft's NTDBLIB.dll) was cancelled by Bill Gates in SQLServer v7. It was supported in 2000 - but ANSI only with limited functionality and dropped for SS2000 and 2005 in the Unicode world. So your choices are: ODBC, OLE-DB and ADO.net. There is new SNC driver for PB 11 that is simil...

Migrate Database from SQL Server 2005 beta 3 to Sql Server 2005 Release
Do you now, How can I move database with datas to release version of sql server? Procedores backup/restore can't help becouse of unsuported version database. Any ideas? What about generate a schema script?  Then run an install of your schema in a new query.  You could at least get the db schema but not the data.Eric RamseurRainbow Portal 2.0 AdminDownload Rainbow 2.0!!Rainbow Code Rainbow Portal CommunityC# 2005 Group but I have many datas...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

moving data from vb.net 1.1 Datagrid to sql server 2005 database
Hi, I was hoping someone could help me with a problem I am having. I can get the datagrid to populate from the database fine, like so:     Public Sub BindTheData()         Dim strConn As String        strConn = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")        Dim objConn As SqlConnection        objConn = New SqlConnection(strConn)        Dim myCmd As SqlComman...

If I upgrade sql server 2005 express to sql server 2008 express, will my user instances in the 2005 database be transfered automaticly?
If I upgrade sql server 2005 express to sql server 2008 express, will my user instances in the 2005 database be transfered automaticly? Thanks! Hi asdpai, I am not sure if I catch your meaning.But if you want to upgrade the SQL Server 2005 Express to SQL Server 2008 Express, you may refer to the following link: Version and Edition Upgradeshttp://msdn.microsoft.com/en-us/library/ms143393.aspxHow to: Upgrade to SQL Server 2008 (Setup)http://msdn.microsoft.com/en-us/library/ms144267.aspxJian KangMicrosoft Online Community SupportPlease remember to mark the replies as answers if t...

Smoke [5.10.1] perl-5.10.1-RC1-1-gbadf489 FAIL(F) netbsd 5.0.1 (i386/1 cpu)
Automated smoke report for 5.10.1 patch badf48949ef0f6be41c06a682330f4aa37c4f4ff perl-5.10.1-RC1-1-gbadf489 p5netbsd: Intel 686-class (i386/1 cpu) on netbsd - 5.0.1 using cc version 4.1.3 20080704 prerelease (NetBSD nb2 20081120) smoketime 9 hours 14 minutes (average 1 hour 9 minutes) Summary: FAIL(F) O = OK F = Failure(s), extended report at the bottom X = Failure(s) under TEST but not under harness ? = still running or test results not (yet) available Build failures during: - = unknown or N/A c = Configure, m = make, M = make (after miniperl), t = ...

Can you have a single SQL Session State Server/database for both 1.1 and 2.0 applications?
Hi,Our production server farm consists of a number of web servers, all running both ASP.NET 1.1 and ASP.NET 2.0. The database server is running SQL Server 2000 and has the .NET 1.1 Framework installed. We use a database named ASPState to persist session state.We only recently upgraded to ASP.NET 2.0 and all the sites are currently using ASP.NET 1.1.We'd like to start upgrading sites to use ASP.NET 2.0 but before this is possible, we need to find a solution to this error:"System.Web.HttpException: Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed o...

EA Server 6.0.1.14 EBF14587 and Powerbuilder 10.5.1 6662
We logged the bug in EA Server regarding inter-component function calls on EA Server 6.0. The bug was fixed in EBF14587. However, a fix to Powerbuilder was required to get it to work. So, I've got the powerbuilder 10.5.1 6662 installed on my machine and I've got everything working. My EA Server and Powerbuilder use the same Powerbuilder runtime. BUT, when I deploy the application (Windows 2003 Server Virtual) I get a powerbuilder error BREAKPOINT EXCEPTION when our app does the intercomponent call. So, I believe the remote server is having Powerbuilder runtime issues. I h...

Smoke [5.10.1] perl-5.10.1-1-gca8de22 FAIL(M) Solaris 2.10 (i386/1 cpu)
Automated smoke report for 5.10.1 patch ca8de220718ba91d5a5fdd9779497cd5b0250258 perl-5.10.1-1-gca8de22 unknown: i86pc (2300MHz) (i386/1 cpu) on Solaris - 2.10 using /opt/SUNWspro/bin/cc version Sun C 5.9 SunOS_i386 2007/05/03 smoketime 9 minutes 24 seconds (average 9 minutes 24 seconds) Summary: FAIL(M) O = OK F = Failure(s), extended report at the bottom X = Failure(s) under TEST but not under harness ? = still running or test results not (yet) available Build failures during: - = unknown or N/A c = Configure, m = make, M = make (after miniperl), t...

Restoring a SQL Server 2005 database into SQL Server 2000
Hey guys  We currenlty have SQL Server 2000 installed inhouse for testing.  A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database?  thanks Hi,Ask your client to backup database as 2000 format. There is an option for this in Microsoft SQL Server Managment Studio. Then download MSSMSE and install for yourself and try to restore database from it.If database doesn't use any 2005 features (rowcount for exampl...

How to convert a SQL Server 2005 Database to SQL Server Express
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1\SQLEXPRESS'.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOp...

Web resources about - SQL Server 2005 - PB 10.5.1 with Application Role - sybase.powerbuilder.database

Application - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Pre-Christmas job application rush hits Australian public service
Speed required for pre-Christmas job window opening for public servants. Check out who is hiring.

Second visa application 'likely' to be approved
The family of a terminally ill Pakistani student can 'hopefully come to Australia sooner rather than later' says Immigration Minister Peter Dutton. ...

Cloud application adoption increases 71 percent in 2015
The adoption of cloud applications is soaring, up by 71 percent in the last year according to the results of a new survey. The latest Cloud ...

Tashfeen Malik's visa application under scrutiny - Business Insider Deutschland
The US government has released the visa application for Tashfeen Malik, who along with her husband killed 14 people in an attack in San Bernardino, ...

MBA: Mortgage Applications Decrease in Latest MBA Weekly Survey, Purchase Applications up 34% YoY
From the MBA: Mortgage Applications Decrease in Latest MBA Weekly Survey Mortgage applications decreased 1.1 percent from one week earlier, ...

Lawmaker: Immigration Officials Didn’t Fully Vet Tashfeen Malik’s Visa Application
"It’s unacceptable that U.S. Citizenship and Immigration Services did not fully vet Malik’s application and instead sloppily approved her visa," ...

German-Based Spirea Promotes Decentralized PV Applications In Uruguay
... Its technical operations specialist, Laurie Lawrence, pointed out that Uruguay [&hellip German-Based Spirea Promotes Decentralized PV Applications ...

Visa application of San Bernardino shooter released
CNN Visa application of San Bernardino shooter released CNN (CNN) The visa application of one of the San Bernardino shooters was released ...

ZTE to develop smartphone application processors in house
ZTE has stepped up its efforts to internally develop smartphone chipset solutions, aiming to re-capture the title as one of the top-three smartphone ...

Resources last updated: 12/27/2015 1:53:47 AM