Using Windows SignOn with PowerBuilder and SQL Server

Submitted by Bevan on Tue, 09/03/2010 - 09:48

Following The Good Samaritan Principle, here's a tip for people using PowerBuilder against a SQL Server back end with single-sign-on.

If you want your PowerBuilder 8.5 application to use Windows Authentication with your SQL Server 2008 connection, here's code that works:

/* Connect by authentication - Populate SQLCA */
SQLCA.DBMS = "MSS Microsoft SQL Server 2008"
SQLCA.ServerName = "MyServer"
SQLCA.Database = "MyDatabase"
// Do NOT! alter the order of these two DBParm statements!
SQLCA.DBParm = "TrustedConnection=1"
SQLCA.DBParm = "Secure=1" 
CONNECT

This code was developed by a colleague and friend, Renwick Wright, after many hours of frustration. Blogged that others might find (courtesy of Professor Google or Doctor Bing) and be able to keep their hair.

Comments

by Bob Fritz - 2012-01-10 12:12

You can also

You can also use:

SQLCA.DBParm = "TrustedConnection=1,Secure=1"

instead of the two DBParm statements listed.

by Bevan - 2012-01-10 17:53

Works for you?

That's interesting - when a colleague and I were originally trying to get this to work, using a single DBParm statement in the style you show didn't work for us.

Glad it works for you though!

by Bob Fritz - 2012-01-11 04:48

Yes, it works perfectly!

Yes, it works perfectly! PowerBuilder 8.0.4, SQL Server 2008 R2

I tried other characters to separate other than a comma, but everything else fails.

I found it odd that you would assign a different value twice to a variable. As far as I know, when you do the following:
var = "123"
var = "456"
the end result is that var = "456" and it's like the assignment to "123" didn't happen.

PowerBuilder must be parsing the SQLCA.DbParm assignment differently than a regular variable assignment. It probably calls a function behind the scenes to prime an internal structure or something when it comes across DbParm. Very interesting - I'm actually surprised your way worked. :) (Your way did work for me too, but for maintenance purposes, I prefer the one assignment. Less confusing for downstream programmers and you can store the value in your system ini file if desired.) In my 18 years of PowerBuilder experience, this way of assigning DbParm really surprised me! You learn something new every day....

by Bevan - 2012-01-11 11:13

There's always something to learn

I agree - PowerBuilder is doing something odd with the assignments to DbParm; I suspect your thoughts on building a behind-the-scenes data structure are spot on.

Having a single assignment is certainly easier to read, and less jarring for anyone performing maintenance later on. We handled this with some LARGE comments around the code explaining what's going on - effectively a "Here be dragons!" warning.