PowerBuilder / SQL Server / NT Authentication

I've assigned 500 because this is a fairly urgent question.  We're using PowerBuilder 8 at the moment but can upgrade to 10 if need be.  We need to connect to a SQL Server and have that connection run through NT authentication instead of using a SQL user.  I tried while setting up the connection to leave the username/password blank hoping that it would then default to the NT Authentication from the user logged into the machine but it did not.  I need to know step by step how to go about doing this.  Thanks in advance.
LVL 3
IUFITSAsked:
Who is Participating?
 
diasroshanCommented:
hi ,
Please refer the URL... it is very helpful for u...
http://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_21006549.html?query=NT+authentication&topics=92

to make things short.... add the following code in ur application open event... it will create a DSN for u dynamically and u can connect to SQL Server thru NT authentication(provided the username has rights on SQL Server )

// Define the following as the Global External Function in the application
Function boolean GetUserNameA( ref string userID, ref ulong len ) library "ADVAPI32.DLL"


//Write the following code in application Open Event....

// set system dsn
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","Database","security_sql")
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","Description","Security")
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","Driver","C:\WINNT\System32\sqlsrv32.dll")
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","Server","sdmsrv")


string  login_name
string  ls_temp
ulong   lul_value
boolean lb_rc

lul_value = 255
ls_temp = Space( 255 )
lb_rc = GetUserNameA( ls_temp, lul_value )
login_name = Trim( ls_temp )

// login_name will return the windows username who has logged onto the machine
// If the username has rights on SQL Server he can successfully login using windows authentication

RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","LastUser",login_name)
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","Trusted_Connection","Yes")

// if u want SQL server authentication (NOT NT authentication) ignore the above 2 lines and add the following line..
RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\security_sql","LastUser","sa")


SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=security_sql"

Connect using SQLCA;
//code ends here

the above code will create ur DSN 'security_sql' on the machine if it doesn't find the same else it will overwrite the DSN.... the code is tested and works fine.... if u r still encountering any error please state the error so i can help...

...do ask if u need more clarity...

Cheers,
Rosh
0
 
IUFITSAuthor Commented:
Our users won't have access to write to the registry (this application goes outside of our active directory OU but not out of the domain) so I'm going to attempt to take the DSN you create and keep it in the program.  I'll report back cause I'm sure I'll have a question but that was a great post Rosh, thanks!
0
 
michaelstoffelCommented:
You don't mention which interface you are using (ODBC, MSS, OLEDB).  Have you tried adding 'Secure=1' in the dbparm of the transaction object?  That is all you need for MSS.
0
 
diasroshanCommented:
hi,

do u need any more assistance???


Cheers,
Rosh
0
 
IUFITSAuthor Commented:
I'll be able to update you hopefully later today.  Thanks for the patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.