Very Computer

by **Jon Dowel** » Fri, 13 Feb 1998 04:00:00

If you configure the service to run under an NT account with permissions on
the SQL Server (and the server is using integrated or mixed security), then
you are done. Don't specify the userID/password, NT will deal with it.

-Jon

>Hi,
>I have an NT service which opens an SQL SERVER database using ADO. To
>open the database I need a UID and PWD. Since, I am running it from an
>NT service I can't display any dialogbox. One way to solve this is to
>hard code the UID and PWD in the code OR to store it in the registry.
>Obvisouly, this is unacceptable.

>One way I can see of solving this, is to, start the service in a user
>account (not System Account) and use this security context to open the
>database. Unfortunatly, I can only have the SID and there is no way of
>using SID to open database.

>Got any better solution?

>Thanks,
>Rukesh

by **Jeff A. Stucker [MVP** » Fri, 13 Feb 1998 04:00:00

With Oracle and SQL Server, you can use NT authentication a.k.a. external
authentication a.k.a. integrated authentication. This opens the database
connection using the security of the caller (in this case the user identity
of the security context). This is the basis for our MTS objects opening the
database (we have Oracle). Works great.

'(' Jeff
\

>Hi,
>I have an NT service which opens an SQL SERVER database using ADO. To
>open the database I need a UID and PWD. Since, I am running it from an
>NT service I can't display any dialogbox. One way to solve this is to
>hard code the UID and PWD in the code OR to store it in the registry.
>Obvisouly, this is unacceptable.

>One way I can see of solving this, is to, start the service in a user
>account (not System Account) and use this security context to open the
>database. Unfortunatly, I can only have the SID and there is no way of
>using SID to open database.

>Got any better solution?

>Thanks,
>Rukesh

by **Rukesh Pate** » Sat, 14 Feb 1998 04:00:00

Hi,
I have an NT service which opens an SQL SERVER database using ADO. To
open the database I need a UID and PWD. Since, I am running it from an
NT service I can't display any dialogbox. One way to solve this is to
hard code the UID and PWD in the code OR to store it in the registry.
Obvisouly, this is unacceptable.

One way I can see of solving this, is to, start the service in a user
account (not System Account) and use this security context to open the
database. Unfortunatly, I can only have the SID and there is no way of
using SID to open database.

Got any better solution?

Thanks,
Rukesh

by **Rukesh Pate** » Sat, 14 Feb 1998 04:00:00

Jon,
Thanks for responding.
I have tried this and it works. However, I was confused about integrated and
mixed security. What does this mean?
Thanks for you help.
Rukesh

> If you configure the service to run under an NT account with permissions on
> the SQL Server (and the server is using integrated or mixed security), then
> you are done. Don't specify the userID/password, NT will deal with it.

> -Jon

> >Hi,
> >I have an NT service which opens an SQL SERVER database using ADO. To
> >open the database I need a UID and PWD. Since, I am running it from an
> >NT service I can't display any dialogbox. One way to solve this is to
> >hard code the UID and PWD in the code OR to store it in the registry.
> >Obvisouly, this is unacceptable.

> >One way I can see of solving this, is to, start the service in a user
> >account (not System Account) and use this security context to open the
> >database. Unfortunatly, I can only have the SID and there is no way of
> >using SID to open database.

> >Got any better solution?

> >Thanks,
> >Rukesh

--
Rukesh Patel
Applied Voice Technologies Pty. Ltd. | PH: +61 (2) 99295988
161 Walker Street, North Sydney, | FAX: +61 (2) 99295980

by **Jon Dowel** » Sat, 14 Feb 1998 04:00:00

A SQL Server can be running in one of three security modes:

Standard, where you set up login ID's and passwords for users in SQL server
(this is where you pass in the UID and PWD).

Integrated, where SQL server just uses NT accounts to determine what rights
you have in the database (for instance, members of the NT Admin group might
be considered sa in SQL server).

Mixed, where you can do it either way.

To log in without passwords, the server must be running under integrated or
mixed mode.
-Jon

>Jon,
>Thanks for responding.
>I have tried this and it works. However, I was confused about integrated
and
>mixed security. What does this mean?
>Thanks for you help.
>Rukesh

>> If you configure the service to run under an NT account with permissions
on
>> the SQL Server (and the server is using integrated or mixed security),
then
>> you are done. Don't specify the userID/password, NT will deal with it.

>> -Jon

>> >Hi,
>> >I have an NT service which opens an SQL SERVER database using ADO. To
>> >open the database I need a UID and PWD. Since, I am running it from an
>> >NT service I can't display any dialogbox. One way to solve this is to
>> >hard code the UID and PWD in the code OR to store it in the registry.
>> >Obvisouly, this is unacceptable.

>> >One way I can see of solving this, is to, start the service in a user
>> >account (not System Account) and use this security context to open the
>> >database. Unfortunatly, I can only have the SID and there is no way of
>> >using SID to open database.

>> >Got any better solution?

>> >Thanks,
>> >Rukesh

>--
>Rukesh Patel
>Applied Voice Technologies Pty. Ltd. | PH: +61 (2) 99295988
>161 Walker Street, North Sydney, | FAX: +61 (2) 99295980

by **Tibor Karasz** » Sat, 14 Feb 1998 04:00:00

A comment on standard security:

It's perfrectly possible to have SQL Server running in standard security
and use LoginId assignment based on your NT account.
The client app has to "ask" for a Client Reqiested Trusted Connection, a
property set at connect time.
ISQL/w and E.M.'s "Use Trusted Connection" does exactly this.

You need to get to the source code, though...
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB

> A SQL Server can be running in one of three security modes:

> Standard, where you set up login ID's and passwords for users in SQL
server
> (this is where you pass in the UID and PWD).

> Integrated, where SQL server just uses NT accounts to determine what
rights
> you have in the database (for instance, members of the NT Admin group
might
> be considered sa in SQL server).

> Mixed, where you can do it either way.

> To log in without passwords, the server must be running under integrated
or
> mixed mode.
> -Jon

> >Jon,
> >Thanks for responding.
> >I have tried this and it works. However, I was confused about integrated
> and
> >mixed security. What does this mean?
> >Thanks for you help.
> >Rukesh

> >> If you configure the service to run under an NT account with
permissions
> on
> >> the SQL Server (and the server is using integrated or mixed security),
> then
> >> you are done. Don't specify the userID/password, NT will deal with it.

> >> -Jon

> >> >Hi,
> >> >I have an NT service which opens an SQL SERVER database using ADO. To
> >> >open the database I need a UID and PWD. Since, I am running it from
an
> >> >NT service I can't display any dialogbox. One way to solve this is to
> >> >hard code the UID and PWD in the code OR to store it in the registry.
> >> >Obvisouly, this is unacceptable.

> >> >One way I can see of solving this, is to, start the service in a user
> >> >account (not System Account) and use this security context to open
the
> >> >database. Unfortunatly, I can only have the SID and there is no way
of
> >> >using SID to open database.

> >> >Got any better solution?

> >> >Thanks,
> >> >Rukesh

> >--
> >Rukesh Patel
> >Applied Voice Technologies Pty. Ltd. | PH: +61 (2) 99295988
> >161 Walker Street, North Sydney, | FAX: +61 (2) 99295980

by **George Lamber** » Sat, 14 Feb 1998 04:00:00

In the case of SQL Server and MTS, who would the user be when I am accessing
user information from within a stored procedure? Would it be the caller
identity or would it be some kind of anonymous login used by MTS? Same
question would apply to Oracle as well.

Thanks,
George

Quote:>With Oracle and SQL Server, you can use NT authentication a.k.a. external
>authentication a.k.a. integrated authentication. This opens the database
>connection using the security of the caller (in this case the user identity
>of the security context). This is the basis for our MTS objects opening
the
>database (we have Oracle). Works great.

>'(' Jeff

by **Jeff A. Stucker [MVP** » Sat, 14 Feb 1998 04:00:00

It's the identity of the package that logs into the database--unless you do
some kind of impersonation within the MTS component, which is not
recommended, because it's not scaleable and thwarts connection pooling.

'(' Jeff
\

>In the case of SQL Server and MTS, who would the user be when I am
accessing
>user information from within a stored procedure? Would it be the caller
>identity or would it be some kind of anonymous login used by MTS? Same
>question would apply to Oracle as well.

>Thanks,
>George

>>With Oracle and SQL Server, you can use NT authentication a.k.a. external
>>authentication a.k.a. integrated authentication. This opens the database
>>connection using the security of the caller (in this case the user
identity
>>of the security context). This is the basis for our MTS objects opening
>the
>>database (we have Oracle). Works great.

>>'(' Jeff

by **Jason Goema** » Wed, 18 Feb 1998 04:00:00

You could also encrypt the password.

On Thu, 12 Feb 1998 17:51:35 -0600, "Jon Dowell"

>If you configure the service to run under an NT account with permissions on
>the SQL Server (and the server is using integrated or mixed security), then
>you are done. Don't specify the userID/password, NT will deal with it.

>-Jon

>>Hi,
>>I have an NT service which opens an SQL SERVER database using ADO. To
>>open the database I need a UID and PWD. Since, I am running it from an
>>NT service I can't display any dialogbox. One way to solve this is to
>>hard code the UID and PWD in the code OR to store it in the registry.
>>Obvisouly, this is unacceptable.

>>One way I can see of solving this, is to, start the service in a user
>>account (not System Account) and use this security context to open the
>>database. Unfortunatly, I can only have the SID and there is no way of
>>using SID to open database.

>>Got any better solution?

>>Thanks,
>>Rukesh

by **D. Nguye** » Thu, 13 Aug 1998 04:00:00

How do you configure the servcice to run under an NT account? I thought by
default it runs as administrator? The reason I am asking is I can connect
via ISQL/w, Enterprise Manager, Access etc. to my SQL database but I keep
getting the following error when I try to query via the web:

Quote:> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed- User: _

Reason: Not defined as a

Quote:> valid user of a trusted SQL Server connection.

I tried using the following to no avail...

Quote:> rs.Open sql,

"DSN=keyindicators;uid=sa;password=12345;database=keyindicators"

Any suggestions?

>If you configure the service to run under an NT account with permissions on
>the SQL Server (and the server is using integrated or mixed security), then
>you are done. Don't specify the userID/password, NT will deal with it.

>-Jon

>>Hi,
>>I have an NT service which opens an SQL SERVER database using ADO. To
>>open the database I need a UID and PWD. Since, I am running it from an
>>NT service I can't display any dialogbox. One way to solve this is to
>>hard code the UID and PWD in the code OR to store it in the registry.
>>Obvisouly, this is unacceptable.

>>One way I can see of solving this, is to, start the service in a user
>>account (not System Account) and use this security context to open the
>>database. Unfortunatly, I can only have the SID and there is no way of
>>using SID to open database.

>>Got any better solution?

>>Thanks,
>>Rukesh

by **Keith Kratochvi** » Sat, 15 Aug 1998 04:00:00

You may want to look into the SQL Server security model. It may be set to
'Windows NT Integrated.' If so, I am guessing that the Default Login set
within SQL does not match the user that the web server is sending over to
SQL for authentication. You may need to add or modify the user that the web
server is sending to SQL so that the default logins and passwords match.

If this does not work, try setting the SQL Security model to 'Standard'.
You will probably now be able to log into SQL via the web project, but you
may not be able to log into SQL throug ISQL_w or Enterprise Manager (it
depends on how you are logging in through these tools).

Keith

>How do you configure the servcice to run under an NT account? I thought by
>default it runs as administrator? The reason I am asking is I can connect
>via ISQL/w, Enterprise Manager, Access etc. to my SQL database but I keep
>getting the following error when I try to query via the web:

>> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed- User: _
>Reason: Not defined as a
>> valid user of a trusted SQL Server connection.

>I tried using the following to no avail...

>> rs.Open sql,
>"DSN=keyindicators;uid=sa;password=12345;database=keyindicators"

>Any suggestions?

>>If you configure the service to run under an NT account with permissions
on
>>the SQL Server (and the server is using integrated or mixed security),
then
>>you are done. Don't specify the userID/password, NT will deal with it.

>>-Jon

>>>Hi,
>>>I have an NT service which opens an SQL SERVER database using ADO. To
>>>open the database I need a UID and PWD. Since, I am running it from an
>>>NT service I can't display any dialogbox. One way to solve this is to
>>>hard code the UID and PWD in the code OR to store it in the registry.
>>>Obvisouly, this is unacceptable.

>>>One way I can see of solving this, is to, start the service in a user
>>>account (not System Account) and use this security context to open the
>>>database. Unfortunatly, I can only have the SID and there is no way of
>>>using SID to open database.

>>>Got any better solution?

>>>Thanks,
>>>Rukesh

by **Eric Raski** » Sat, 15 Aug 1998 04:00:00

As Jon Dowell wrote, assuming you are using "Integrated" security, you
should create a User in the Domain specifically for this service. Create
the same username/password combination as an SQL Server login and give it
access to the appropriate database objects. Double check that the correct
mapping exists in the SQL Security manager from the domain login to the SQL
login, and create it if necessary. Then, be sure that the service is running
with this domain user/password combination (can be set by CreateService() or
by Control Panel - Services Applet). Don't specify any username/password in
the connection string.

This should solve the security issue.

Now for a question of my own (sorry if this is a breach of netiquette).
I've tried to do exactly the same thing -- create an NT service in VC++
using ADO to talk to an SQL Server. I'm not getting the same problem --
instead I'm getting a different error where the service can't open the first
connection. It's not in ODBC, but in ADO itself (it appears). What happens
is I get an exception with the following error message:

The system could not find the environment option that was set.

This is error #203 in winerror.h. The exact same software, with no changes
at all, runs fine as an interactive task (from the command line in a "debug"
mode). It only fails when executed as a service.

To eliminate ODBC as the culprit, I turned on ODBC tracing. No entries were
made before the error occurs. I also tried using the OLE DB data provider
(SQLOLEDB) instead. The exact same things occured. Do you have any ideas
for me?

Eric Raskin

P.S. I've been working this problem under the thread named "ADO in NT
Service fails!". Feel free to post there or e-mail me with any suggestions
at all. It's been three tough days on this problem so far!

>> Microsoft OLE DB Provider for ODBC Drivers error '80004005'

>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed- User: _
>Reason: Not defined as a
>> valid user of a trusted SQL Server connection.

>I tried using the following to no avail...

>> rs.Open sql,
>"DSN=keyindicators;uid=sa;password=12345;database=keyindicators"

>Any suggestions?

>>If you configure the service to run under an NT account with permissions
on
>>the SQL Server (and the server is using integrated or mixed security),
then
>>you are done. Don't specify the userID/password, NT will deal with it.

>>-Jon

Very Computer

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!

Open database without UID and PWD!!