Connecting from dll to SQL Server via Integrated Security.

[mickieparis]

Hello,
I’m creating a component (dll) that will run on a component server
that needs to connect to MS SQL server and retrieve data. The
connection needs to be made via Integrated Security. I’ve created
components in the past that used a connection string that specified
the user name and password but never Integrated Security. With the
user name/pw conn string I just needed to make sure that user had
access to the sql server and the database and all worked well. Now
with SSPI do I need to create a windows account in the domain just for
the component and give that account access to sql sever and the db? I
don’t want the component to run under my account or any real person’s
account. Is that how most firms setup components that need to use
SSPI? Also, how do setup the dll to make sure it runs under that
account?

Thanks.
Michelle

 

[Arne Vajhøj]

I’m creating a component (dll) that will run on a component server
that needs to connect to MS SQL server and retrieve data. The
connection needs to be made via Integrated Security. I’ve created
components in the past that used a connection string that specified
the user name and password but never Integrated Security. With the
user name/pw conn string I just needed to make sure that user had
access to the sql server and the database and all worked well. Now
with SSPI do I need to create a windows account in the domain just for
the component and give that account access to sql sever and the db? I
don’t want the component to run under my account or any real person’s
account. Is that how most firms setup components that need to use
SSPI? Also, how do setup the dll to make sure it runs under that
account?

If that DLL is a standard .NET assembly, then it will run under the same
identity as the EXE calling it.

That is how components are usually working.

But maybe your "component server" offers some capabilities
in this regard. We have no way of knowing based on the information
provided.

Arne

 

[J.B. Moreno]

Hello,
Iím creating a component (dll) that will run on a component server
that needs to connect to MS SQL server and retrieve data. The
connection needs to be made via Integrated Security. Iíve created
components in the past that used a connection string that specified
the user name and password but never Integrated Security. With the
user name/pw conn string I just needed to make sure that user had
access to the sql server and the database and all worked well. Now
with SSPI do I need to create a windows account in the domain just for
the component and give that account access to sql sever and the db? I
donít want the component to run under my account or any real personís
account. Is that how most firms setup components that need to use
SSPI? Also, how do setup the dll to make sure it runs under that
account?

You can't set the account of a DLL, you can set the account of a
process. Generally processes are started one of three ways: a windows
service that starts at boot time, a windows scheduled job, a sql
scheduled job.

How you set which account is used will depend upon which of the three
methods that you use. As for whether you have to create an account
just for the component, that depends upon your circumstances.

 

[mickieparis]

Hi, and thanks all for responding.

The dll will be called by an exe. As Arne pointed out, the dll will
run under the identity of the exe. The exe I believe will run under
the context of the user who logged into the server and started a
session in which the exe was executed. Now to use sql integrated
security, whatever user the exe is running under must have an account
setup on the sql server that allows them to log into the server AND
the database so the data access layer dll (remember the dll way back
in the chain...) can execute stored procs. And the user must have
access to the sql schema under which the stored proc lives.

I hope I have this right...

MP

 

[J.B. Moreno]

Hi, and thanks all for responding.

The dll will be called by an exe. As Arne pointed out, the dll will
run under the identity of the exe. The exe I believe will run under
the context of the user who logged into the server and started a
session in which the exe was executed.

If there is an actual user involved, yes. It sounded like you wanted
an automatic operation of some sort.

Now to use sql integrated security, whatever user the exe is running
under must have an account setup on the sql server that allows them
to log into the server AND the database so the data access layer dll
(remember the dll way back in the chain...) can execute stored procs.
And the user must have access to the sql schema under which the
stored proc lives.

I hope I have this right...

Not quite. Assuming that you're operating within a domain, you
normally have the user be a member of a group that has such access.
This allows you to centralize your access control and security.