Connecting to Microsoft SQL using Windows Authentication

[DanHarper]

I am using powerbuilder v.9 build 5507 to connect to
Microsoft SQL Server 2000 - 8.00.2039 (Build 3790: Service Pack 1)

My problem is I have to use a windows authentication to connect to a new
data source. I have used the SQL server authentication in the past, and
passed the SA user and password and it's worked fine. Now my DBA set up
a windows user that looks like it has all the correct access, but I am
not sure how to pass the parameters.

Is it something like user: Server.database_name.domain\user
and the password?

TIA for any help

Dan

[Tyler Cruse]

You will need to indicate integrated security in your connection string and
do not send username or password.

"DanHarper" <DHa...@research.umass.edu> wrote in message
news:45ae7d52$1@forums-1-dub...

[dan harper]

Sorry, you lost me already.
I have made a database connection in the database profile painter. I
chose SQL, entered the name of the server, user name and password (Using
the SA account)
I then copied the syntax to my connection object, and call that object
(SQLCA) when I need to connect to SQL. I guess more specifically, I need
to know what code to use to indicate integrated security in my
connection object, w/o passing a user or PW.

I can post the connection string I use if that helps.

ps if I'm reading it right, say I can get it to accept domain\my_name
because i am a windows user in SQL. I have 20 other users who are NOT in
the sql users group. If they ran the program indicating integrated
security, wouldn't sql then look at THEIR user name and PW, realize they
are NOT in the sql users and return an access error?

Thanks again,

Dan

[ArmanPorky]

Hi Dan,

Just recently tried your scenario using PB9 and MSSQL2K. The
driver I used is MSS (Native DB Connection). If your DBA had
set-up a user using Windows authentication, say for my test
CHAMBER\amosdev, in your PB application you just need to add
"Secure=1" to your DBParm transaction object property. This
indicates integrated security. In LogID and LogPass, you
should not pass anything (passing something like
CHAMBER\sql_group, will not make any difference).

But as you've said, you have 20 users not member of the sql
group. If I understand you correctly, if that user group is
the one that your DBA had given the access to your server,
you have two choices.

* I assume that the so called sql group is a Windows domain
group that had been granted access to that SQL Server box,
here I call it SQL_GROUP on MyDomain (thus represented as
MyDomain\SQL_GROUP upon creation of login in MS-SQL). On PB
Application part, the above explanation on connection
parameters is sufficient.

Choice 1: Your system administrator make those users member
of that group (easiest).

Choice 2: If your users does not directly connect to that
particular server via drive mapping or any other network
connection, have your system administrator create a generic
account, say SQL_User, and make it a member of SQL_Group.
Then, you have to create a network connection either via
Drive Mapping or by using NET USE command line utility.
Below is a sample of how to use it via NET USE without
mapping it to any drive.

ex. NET USE \\<sql_server_machine>\IPC$ <password>
/USER:MyDomain\SQL_User

This will create an initial connection to that server. Any
subsequent network connection to that machine will use those
credentials (MyDomain\SQL_User, <password>). If you want to
automate this, you can create a batch file (or include in
AUTOEXEC.BAT) that will perform the above, OR, you can put
it in the registry,
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run
, by creating a new string value (say SQL_Connect) in that
key and assigning your NET USE command as the value.

For my trial case, I have a network connection to the SQL
Server machine (CHAMBER) and have a user (devuser) set-up.
So even if I persist on passing any user on my Transaction
object, as long as "Secure=1" is set in DBParm, the DB
connection still uses CHAMBER\devuser.

HTH,
Arman

[Scott Morris]

"dan harper" <dha...@research.umass.edu> wrote in message
news:45aef6a5$1@forums-1-dub...

> Sorry, you lost me already.
> I have made a database connection in the database profile painter. I chose
> SQL, entered the name of the server, user name and password (Using the SA
> account)
> I then copied the syntax to my connection object, and call that object
> (SQLCA) when I need to connect to SQL. I guess more specifically, I need
> to know what code to use to indicate integrated security in my connection
> object, w/o passing a user or PW.

To reiterate (and clarify) Arman's comments:
Check the integrated security box on the network tab, then review the effect
of this change in the preview tab.

> ps if I'm reading it right, say I can get it to accept domain\my_name
> because i am a windows user in SQL. I have 20 other users who are NOT in
> the sql users group. If they ran the program indicating integrated
> security, wouldn't sql then look at THEIR user name and PW, realize they
> are NOT in the sql users and return an access error?

No - if you have set the connection for integrated security, then the userID
and password are ignored - always. If you need to allow users to use either
security option, then you will need to figure out how to do this at the
workstation level. QA lets you select which method to use - perhaps you can
emulate this interface.

[DanHarper]

THANK YOU ALL!
I am usually more stubborn and figure these things out, but i have to
compile and roll out this afternoon!

Thanks again, most helpful!