Windows service

[Rotsey]

Hi,

I have a windows service that does some distributed transcation
across 2 servers. One DB is SQL 2005 express and the other is SQL 2005.

My question is I have created a domain user account and I am
logging the service in as that account. Then I am adding permission to
the user acccount to both DB's.
If I use integrated security in my connection strings to the
DB's will they login as the domain user account and
everything be greart????

Malcolm

 

[Bela Istok]

I guest is OK,do you try that?

Regards

Bela Istok

 

[Rotsey]

No have not tried it yet

I am trying to save me some pain by asking

 

[Alberto Poblacion]

I have a windows service that does some distributed transcation
across 2 servers. One DB is SQL 2005 express and the other is SQL 2005.

My question is I have created a domain user account and I am
logging the service in as that account. Then I am adding permission to
the user acccount to both DB's.
If I use integrated security in my connection strings to the
DB's will they login as the domain user account and
everything be greart????

Yes, this should work, and in fact it is the recommended way to configure
security for such a setup.

 

[Mr. Arnold]

No have not tried it yet

I am trying to save me some pain by asking

Yes you should do that with NT Integrated security if the account being used
on
the running machine is established on SQL 2005 server and SQL 2005 express.

However, most would just use a generic user-id and psw for the logins one
for Server and one for Express and make two entries in AppSettings for
connections strings.

 

[Mr. Arnold]

Yes, this should work, and in fact it is the recommended way to
configure security for such a setup.

Really? You want to post some information about this is a recommended
security feature. Have you talked with a SQL DBA about this?

 

[Bela Istok]

Arnold If you store the logins in the App.Config you can compromise the
server if someone get to the machine, the best way is to use integrated in
the service (if the service is running in the user stations).

Regards,

Bela Istok

 

[Mr. Arnold]

Arnold If you store the logins in the App.Config you can compromise the
server if someone get to the machine, the best way is to use integrated in
the service (if the service is running in the user stations).

You do know that you can encrypt a configuration file in .NET 2.0 don't you?

http://www.codeproject.com/useritems/Configuration_File.asp

 

[Chris Mullins [MVP - C#]]

Yup. That's exactly how it works, and is the recommended way to do it.

Terminology wise, it's usually called a "Service Account", rather than a
"User Account", but they're the same thing. Generally service accoutns have
passwords that don't expire...

 

[Chris Mullins [MVP - C#]]

Yes you should do that with NT Integrated security if the account being
used on the running machine is established on SQL 2005 server and SQL 2005
express.

However, most would just use a generic user-id and psw for the logins one
for Server and one for Express and make two entries in AppSettings for
connections strings.

The recommend practice is to use Windows Auth from the service to SQL
Server. It's easier to configure, less maintentance, and much more secure.
In general, you should not use SQL Auth for much of anything these days.

There would be 2 connection strings in the AppConfig, one for each DB. Each
of these connection strings is marked to use Integrated Authentication.

 

[Chris Mullins [MVP - C#]]

You do know that you can encrypt a configuration file in .NET 2.0 don't
you?

http://www.codeproject.com/useritems/Configuration_File.asp

While that's an option, it's a real pain. Worse, it's still less secure that
using Windows Auth to the SQL DB. I've actually done this before (we were
hitting an Oracle DB that didn't support Windows Auth), using both the COM+
method recommend by the PAG group, and using the DPAPI method others have
recommended. It worked, but it's alot of overhead both for the devs and the
sys admins to worry about.

If you can use it, Windows Auth to the SQL Database is better in every way.

 

[Mr. Arnold]

While that's an option, it's a real pain. Worse, it's still less secure
that using Windows Auth to the SQL DB. I've actually done this before (we
were hitting an Oracle DB that didn't support Windows Auth), using both
the COM+ method recommend by the PAG group, and using the DPAPI method
others have recommended. It worked, but it's alot of overhead both for the
devs and the sys admins to worry about.

If you can use it, Windows Auth to the SQL Database is better in every
way.

It's only good for a solution that's running in a domain. It's not good for
a solution that the users are not in a NT domain environment. I have not
seen NT Authenticated user used for a means of connecting to a SQL Server
database by any application, even in a domain. That seems to be a PITA for a
DBA, and I think they avoid it, at least the ones I have worked with. Some
DBA's have a special sequence of characters to kind of encrypt the psw a
kind of their own blend or take on the psw.

 

[Mr. Arnold]

The recommend practice is to use Windows Auth from the service to SQL
Server. It's easier to configure, less maintentance, and much more secure.
In general, you should not use SQL Auth for much of anything these days.

There would be 2 connection strings in the AppConfig, one for each DB.
Each of these connection strings is marked to use Integrated
Authentication.

You might convince me on the little more secure, but not the less
maintenance part of just having a generic user-id and psw that an app uses.

 

[Chris Mullins [MVP - C#]]

You might convince me on the little more secure, but not the less
maintenance part of just having a generic user-id and psw that an app
uses.

Well, SQL Auth is considered to be insecure. It's easily cracked (I've used
the tools, and verified this, much to my client's amazement), doesn't have
any metering on it to prevent brute force attacks, transmits the credentials
to the database in plain-text, and doesn't integrate at all into the
standard security infrastructure already being used by the organization.
There no way to enforce password strength rules, and the passwords are store
as case insensitive hashes. There's no default monitoring of the invalid
password attempts, no automatic account lock-out, etc. There's a ton of
documentation on this found on the web.

All of the DBA's that I work with prefer Windows Auth to SQL Auth for ease
of use reasons as well. It's one less set of passwords to remember, less
configuration in the long run, fewer plain-text passwords floating around in
email & config files. It's much easier to switch between databases without
having to continually enter passwords.

It's also easier to manage in the HR case - when you fire an Admin or a Dev,
you don't have to change all the SQL Passwords, just lock-out his account.
When you hire someone new, you don't have to email them the credentials. You
just add the role to their Active Directory account, and it works.

If you ever want to scare yourself, go download Cain & Able off the web, and
watch the insecure traffic fly across your network. You'll have all the
account credientals in no time. Makeing sure the network layer is secure is
important!

 

[Chris Mullins [MVP - C#]]

[SQL Auth vs Windows Auth]

It's only good for a solution that's running in a domain. It's not good
for a solution that the users are not in a NT domain environment.

I've seen many Production SQL Databases & Apps not joined to a domain that
did this. The SQL Server just configured "WebServer1\NETWORK SERVICE" as the
user name, and granted it acccess to the database. Took about 30 seconds to
have it running.

(We can debate the wisdom of having the database & the SQL Server on the
same network, but that's a completly different argument, and still ends up
with the same solutions working just fine)

I have not seen NT Authenticated user used for a means of connecting to a
SQL Server database by any application, even in a domain.

We have very different expereinces in that regard. I see Windows Auth almost
exclusivly. When I do see SQL Auth used, it's confessed to me in hushed
tones, with downcast eyes, and usually followed with a "but we're fixing
it!" said in an urgent voice!

That seems to be a PITA for a DBA, and I think they avoid it, at least the
ones I have worked with.

Again, we have compeltly different expereince in this regard. The DBA's I
know all much prefer it....

Some DBA's have a special sequence of characters to kind of encrypt the
psw a kind of their own blend or take on the psw.

I would be very concerened about that. Custom Encryption schemes are
notoriously unsecure, and likely used more for Job Security purposes than
anything else. The right tools for the job all exist, and there's no reason
not to use them...

 

[Mr. Arnold]

Well, SQL Auth is considered to be insecure. It's easily cracked (I've
used the tools, and verified this, much to my client's amazement), doesn't
have any metering on it to prevent brute force attacks, transmits the
credentials to the database in plain-text, and doesn't integrate at all
into the standard security infrastructure already being used by the
organization.

Most oraganizations don't know the first thing about secuirty -- not really.

There no way to enforce password strength rules, and the passwords are
store as case insensitive hashes. There's no default monitoring of the
invalid password attempts, no automatic account lock-out, etc. There's a
ton of documentation on this found on the web.

I know about all of this. I have been working on the NT based O/S platform
for many years.

All of the DBA's that I work with prefer Windows Auth to SQL Auth for ease
of use reasons as well. It's one less set of passwords to remember, less
configuration in the long run, fewer plain-text passwords floating around
in email & config files. It's much easier to switch between databases
without having to continually enter passwords.

Most solutions are not swithing between databases. The only way that
switching of databases by an application would be is when the appliacation
moves from dev/test/QA/prod, but they are the same database. The only thing
that changes is is where the database is located.

It's also easier to manage in the HR case - when you fire an Admin or a
Dev, you don't have to change all the SQL Passwords, just lock-out his
account.

You and I both know that most are not doing this.

When you hire someone new, you don't have to email them the credentials.
You just add the role to their Active Directory account, and it works.

I sounds good, but again, you and I both know that they are not doing this.

If you ever want to scare yourself, go download Cain & Able off the web,
and watch the insecure traffic fly across your network. You'll have all
the account credientals in no time. Makeing sure the network layer is
secure is important!

I don't disagree with you. But they are not doing it, and I have been in
those shops.

 

[Mr. Arnold]

<snipped>

You're talking about one aspect, which is the NT Domain environment, an
application running in that environment. It doesn't apply to all situations
like a Web solution, when no user is part of a NT domain that can be
authenticated. So in order to logon to SQL server, the application must use
a generic user-id and psw. It's all SQL Server table authentication for the
user.

 

[Bela Istok]

Hello Arnold, if you know all of this why you recommend to Rotsey not to use
Domain Security?

Because the persons you have worked for doesn't do the work in the right
way, doesn't mean you need to omit the solution at all?


Regards,

Bela Istok

 

[Mr. Arnold]

Hello Arnold, if you know all of this why you recommend to Rotsey not to
use Domain Security?

Because the persons you have worked for doesn't do the work in the right
way, doesn't mean you need to omit the solution at all?

There is my post to the OP. Do you want to point out where I recommended
anything. I don't like it when someone tries to imply that I said something
when I didn't say it. Where did I say that *I recommend*.

 

[Chris Mullins [MVP - C#]]

Well, to be picky, you said:

However, most would just use a generic user-id and psw for the logins one
for Server and one for Express and make two entries in AppSettings for
connections strings.

I don't think this is true at all. Moreso, I consider it an Anti-Pattern in
most cases.

The way you phrased it, your post was read by me as, "I recommend doing SQL
Auth over Windows Auth."

I realize that's not quite what you said, but that is obviously how it's
been interpreted by more than just me.

Due to my interpretation, I posted a response saying that "SQL Auth bad.
Windows Auth good.". Otherwise, I would have skipped the thread with "Arnold
already answered it, and I've nothing constructive to add.".