NT AUTHORITY\ANONY MOUS LOGON --- SQL server

[John J. Hughes II]

I have "Computer A" which is running Win2K3 and MS SQL server. And
"Computer B" which is running a service that I have created. If I change
the service on "Computer B" to run as a user my program is able to connect
to the SQL server on "Computer A" correctly. But if I set the service on
"Computer B" to run as a local system then I get an error connecting to the
SQL on "Computer A" --- "NT AUTHORITY\ANONY MOUS LOGON". I understand why I
have the error but would prefer to keep the service set to local system
rather then running it under a user account.

So the question is how to either reconfigure the SqlConnection connection
string so the user is not anonymous or to tell the SQL server it's ok for
this computer to log in. I don't want to allow anonymous log in to my SQL
server obivously but I am not really happy about using the SQL user name and
password either.

Thanks in advance,
John

[Peter Rilling]

Well, the reason it is called "local" service is because it is designed to
be used within the computer, not across a network.

There is a special account called "NT AUTHORITY\Netwo rkService" which will
allow you to connect remotely. When configuring the service, you can
specify this as your user account (no password needed). This causes Windows
to send the machines domain credentials to sqlserver, therefore SqlServer
would see something like "mydomain\Compu terB$" (dollar sign is important).
Just add this account to SqlServer and you should be able to connect.
"John J. Hughes II" <[e-mail] > wrote in message
news:es******** ******@TK2MSFTN GP14.phx.gbl...

I have "Computer A" which is running Win2K3 and MS SQL server. And
"Computer B" which is running a service that I have created. If I change
the service on "Computer B" to run as a user my program is able to connect
to the SQL server on "Computer A" correctly. But if I set the service on
"Computer B" to run as a local system then I get an error connecting to the SQL on "Computer A" --- "NT AUTHORITY\ANONY MOUS LOGON". I understand why I have the error but would prefer to keep the service set to local system
rather then running it under a user account.

So the question is how to either reconfigure the SqlConnection connection
string so the user is not anonymous or to tell the SQL server it's ok for
this computer to log in. I don't want to allow anonymous log in to my SQL
server obivously but I am not really happy about using the SQL user name and password either.

Thanks in advance,
John

[Willy Denoyette [MVP]]

"Peter Rilling" <pe***@nospam.r illing.net> wrote in message
news:eQ******** *******@TK2MSFT NGP15.phx.gbl.. .

Well, the reason it is called "local" service is because it is designed to
be used within the computer, not across a network.

There is a special account called "NT AUTHORITY\Netwo rkService" which will
allow you to connect remotely. When configuring the service, you can
specify this as your user account (no password needed). This causes
Windows
to send the machines domain credentials to sqlserver, therefore SqlServer
would see something like "mydomain\Compu terB$" (dollar sign is important).
Just add this account to SqlServer and you should be able to connect.

This only works when the machine accounts are registered in the AD
(W2K/W2K3), and where your SQL server SPN (Service Principal Name) is
registered in the AD as well.

Willy.

[John J. Hughes II]

Ok, I guess that sounds logical as far as the local system.

I have several options when installing the service and one of them is
NetworkService which seems to be what you are alluding to. If I change the
service so it's installed as either a NetworkService or a LocalService I get
the following error "Requested registry access is not allowed" which since I
store a bunch of data in the registry causes a problems. I am sure it's a
permission thing but a clue as to what time would be helpful if you know it
off the top of your head.

Thanks,
John
"Peter Rilling" <pe***@nospam.r illing.net> wrote in message
news:eQ******** *******@TK2MSFT NGP15.phx.gbl.. .

Well, the reason it is called "local" service is because it is designed to
be used within the computer, not across a network.

There is a special account called "NT AUTHORITY\Netwo rkService" which will
allow you to connect remotely. When configuring the service, you can
specify this as your user account (no password needed). This causes
Windows
to send the machines domain credentials to sqlserver, therefore SqlServer
would see something like "mydomain\Compu terB$" (dollar sign is important).
Just add this account to SqlServer and you should be able to connect.
"John J. Hughes II" <[e-mail] > wrote in message
news:es******** ******@TK2MSFTN GP14.phx.gbl...

I have "Computer A" which is running Win2K3 and MS SQL server. And
"Computer B" which is running a service that I have created. If I change
the service on "Computer B" to run as a user my program is able to
connect
to the SQL server on "Computer A" correctly. But if I set the service on
"Computer B" to run as a local system then I get an error connecting to

the

SQL on "Computer A" --- "NT AUTHORITY\ANONY MOUS LOGON". I understand why

I

have the error but would prefer to keep the service set to local system
rather then running it under a user account.

So the question is how to either reconfigure the SqlConnection connection
string so the user is not anonymous or to tell the SQL server it's ok for
this computer to log in. I don't want to allow anonymous log in to my
SQL
server obivously but I am not really happy about using the SQL user name

and

password either.

Thanks in advance,
John

[Peter Rilling]

Well, now you get into the opposite problem as "local service", in that
NetworkService is designed to work across the network, not locally.

If you need local resources, then you will need to set those permissions on
any registry keys or folders that your Windows service might need to access.

Again, you would just need to set the ACLs for "NT
AUTHORITY\Netwo rkService."

"John J. Hughes II" <[e-mail] > wrote in message
news:e8******** ******@TK2MSFTN GP14.phx.gbl...

Ok, I guess that sounds logical as far as the local system.

I have several options when installing the service and one of them is
NetworkService which seems to be what you are alluding to. If I change the service so it's installed as either a NetworkService or a LocalService I get the following error "Requested registry access is not allowed" which since I store a bunch of data in the registry causes a problems. I am sure it's a
permission thing but a clue as to what time would be helpful if you know it off the top of your head.

Thanks,
John
"Peter Rilling" <pe***@nospam.r illing.net> wrote in message
news:eQ******** *******@TK2MSFT NGP15.phx.gbl.. .

Well, the reason it is called "local" service is because it is designed to be used within the computer, not across a network.

There is a special account called "NT AUTHORITY\Netwo rkService" which will allow you to connect remotely. When configuring the service, you can
specify this as your user account (no password needed). This causes
Windows
to send the machines domain credentials to sqlserver, therefore SqlServer would see something like "mydomain\Compu terB$" (dollar sign is important). Just add this account to SqlServer and you should be able to connect.
"John J. Hughes II" <[e-mail] > wrote in message
news:es******** ******@TK2MSFTN GP14.phx.gbl...

I have "Computer A" which is running Win2K3 and MS SQL server. And
"Computer B" which is running a service that I have created. If I change the service on "Computer B" to run as a user my program is able to
connect
to the SQL server on "Computer A" correctly. But if I set the service on "Computer B" to run as a local system then I get an error connecting to

the

SQL on "Computer A" --- "NT AUTHORITY\ANONY MOUS LOGON". I understand why

I

have the error but would prefer to keep the service set to local system
rather then running it under a user account.

So the question is how to either reconfigure the SqlConnection

connection string so the user is not anonymous or to tell the SQL server it's ok for this computer to log in. I don't want to allow anonymous log in to my
SQL
server obivously but I am not really happy about using the SQL user

name and

password either.

Thanks in advance,
John

[John J. Hughes II]

Again thank you for your support.

I have worked around the registry problem and added the computer name to the
SQL as a valid log in with access to the correct databases.

Now I have the following error message from the connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."

My connection string is "Data Source=MyServer ;Initial
Catalog=DBData; Integrated Security=SSPI;P ersist Security Info=False;Pack et
Size=4096;Works tation ID=MyWks" which works fine from the user account.

Is this message because my workstation is set as a workgroup and not part of
the Win2K3 domain or do I need to configure the connection string
differently?

Regards,
John

"Peter Rilling" <pe***@nospam.r illing.net> wrote in message
news:ej******** ******@TK2MSFTN GP12.phx.gbl...

Well, now you get into the opposite problem as "local service", in that
NetworkService is designed to work across the network, not locally.

If you need local resources, then you will need to set those permissions
on
any registry keys or folders that your Windows service might need to
access.

Again, you would just need to set the ACLs for "NT
AUTHORITY\Netwo rkService."

"John J. Hughes II" <[e-mail] > wrote in message
news:e8******** ******@TK2MSFTN GP14.phx.gbl...

Ok, I guess that sounds logical as far as the local system.

I have several options when installing the service and one of them is
NetworkService which seems to be what you are alluding to. If I change

the

service so it's installed as either a NetworkService or a LocalService I

get

the following error "Requested registry access is not allowed" which
since

I

store a bunch of data in the registry causes a problems. I am sure it's
a
permission thing but a clue as to what time would be helpful if you know

it

off the top of your head.

Thanks,
John
"Peter Rilling" <pe***@nospam.r illing.net> wrote in message
news:eQ******** *******@TK2MSFT NGP15.phx.gbl.. .

> Well, the reason it is called "local" service is because it is designed to > be used within the computer, not across a network.
>
> There is a special account called "NT AUTHORITY\Netwo rkService" which will > allow you to connect remotely. When configuring the service, you can
> specify this as your user account (no password needed). This causes
> Windows
> to send the machines domain credentials to sqlserver, therefore SqlServer > would see something like "mydomain\Compu terB$" (dollar sign is important). > Just add this account to SqlServer and you should be able to connect.
>
>
> "John J. Hughes II" <[e-mail] > wrote in message
> news:es******** ******@TK2MSFTN GP14.phx.gbl...
>> I have "Computer A" which is running Win2K3 and MS SQL server. And
>> "Computer B" which is running a service that I have created. If I change >> the service on "Computer B" to run as a user my program is able to
>> connect
>> to the SQL server on "Computer A" correctly. But if I set the service on >> "Computer B" to run as a local system then I get an error connecting
>> to
> the
>> SQL on "Computer A" --- "NT AUTHORITY\ANONY MOUS LOGON". I understand why > I
>> have the error but would prefer to keep the service set to local
>> system
>> rather then running it under a user account.
>>
>> So the question is how to either reconfigure the SqlConnection connection >> string so the user is not anonymous or to tell the SQL server it's ok for >> this computer to log in. I don't want to allow anonymous log in to my
>> SQL
>> server obivously but I am not really happy about using the SQL user name > and
>> password either.
>>
>> Thanks in advance,
>> John
>>
>>
>
>

[Willy Denoyette [MVP]]

"John J. Hughes II" <[e-mail] > wrote in message
news:Oe******** ******@TK2MSFTN GP12.phx.gbl...

Again thank you for your support.

I have worked around the registry problem and added the computer name to
the SQL as a valid log in with access to the correct databases.

Now I have the following error message from the connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."

My connection string is "Data Source=MyServer ;Initial
Catalog=DBData; Integrated Security=SSPI;P ersist Security Info=False;Pack et
Size=4096;Works tation ID=MyWks" which works fine from the user account.

Is this message because my workstation is set as a workgroup and not part
of the Win2K3 domain or do I need to configure the connection string
differently?

Regards,
John

I'm unclear on how you were able to (successfully) add a remote machine
account to SQL. Machine accounts are W2K (or higher) domain members and must
be specified as domain\MachineN ame$ where domain is the domain where both
SQL server and client machine are members of. Each machine account is just
like a regular domain user account, so authentication requests are handled
by the DC of the login domain, and as you aren't running in a domain realm
this feature won't work for you.

If you need SQL integrated security to work in a workgroup, you should
create a shadow account on both servers that is an account with the same
identity and password. At the asp.net side you must grant the required
privileges to the local system resources like Filesystem and Registry, on
the SQL side you can grant SQL server access and nothing else.

Willy.

[John J. Hughes II]

"Willy Denoyette [MVP]" <wi************ *@telenet.be> wrote in message
news:ew******** ********@TK2MSF TNGP09.phx.gbl. ..

"John J. Hughes II" <[e-mail] > wrote in message
news:Oe******** ******@TK2MSFTN GP12.phx.gbl...

Again thank you for your support.

I have worked around the registry problem and added the computer name to
the SQL as a valid log in with access to the correct databases.

Now I have the following error message from the connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection."

My connection string is "Data Source=MyServer ;Initial
Catalog=DBData; Integrated Security=SSPI;P ersist Security
Info=False;Pack et Size=4096;Works tation ID=MyWks" which works fine from
the user account.

Is this message because my workstation is set as a workgroup and not part
of the Win2K3 domain or do I need to configure the connection string
differently?

Regards,
John

I'm unclear on how you were able to (successfully) add a remote machine
account to SQL. Machine accounts are W2K (or higher) domain members and
must be specified as domain\MachineN ame$ where domain is the domain where
both SQL server and client machine are members of. Each machine account is
just like a regular domain user account, so authentication requests are
handled by the DC of the login domain, and as you aren't running in a
domain realm this feature won't work for you.

If you need SQL integrated security to work in a workgroup, you should
create a shadow account on both servers that is an account with the same
identity and password. At the asp.net side you must grant the required
privileges to the local system resources like Filesystem and Registry, on
the SQL side you can grant SQL server access and nothing else.

Willy.

Well in regards to add the log in name to the SQL server I just did the
following, no problem.
exec sp_grantlogin 'MyDomain\MyCom puter$'

I had a feeling the domain thing was causing me problems. I am writing and
testing my code on a workstation so there is no second server at this point.
When the code is released it will be on a second server so my current
problem may be mute but it would be nice to test in as close to the same
environment as possible.

I am not using asp.net, this is a normal service which accesses the SQL
server on a second computer which happens to be a Win2K3 server.

Oh well thanks for the pointer, I guess I will read about shadow accounts
today :)

Regard,
John

[Willy Denoyette [MVP]]

"John J. Hughes II" <[e-mail] > wrote in message
news:ew******** ******@TK2MSFTN GP12.phx.gbl...

"Willy Denoyette [MVP]" <wi************ *@telenet.be> wrote in message
news:ew******** ********@TK2MSF TNGP09.phx.gbl. ..

"John J. Hughes II" <[e-mail] > wrote in message
news:Oe******** ******@TK2MSFTN GP12.phx.gbl...

Again thank you for your support.

I have worked around the registry problem and added the computer name to
the SQL as a valid log in with access to the correct databases.

Now I have the following error message from the connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection."

My connection string is "Data Source=MyServer ;Initial
Catalog=DBData; Integrated Security=SSPI;P ersist Security
Info=False;Pack et Size=4096;Works tation ID=MyWks" which works fine from
the user account.

Is this message because my workstation is set as a workgroup and not
part of the Win2K3 domain or do I need to configure the connection
string differently?

Regards,
John

I'm unclear on how you were able to (successfully) add a remote machine
account to SQL. Machine accounts are W2K (or higher) domain members and
must be specified as domain\MachineN ame$ where domain is the domain where
both SQL server and client machine are members of. Each machine account
is just like a regular domain user account, so authentication requests
are handled by the DC of the login domain, and as you aren't running in a
domain realm this feature won't work for you.

If you need SQL integrated security to work in a workgroup, you should
create a shadow account on both servers that is an account with the same
identity and password. At the asp.net side you must grant the required
privileges to the local system resources like Filesystem and Registry, on
the SQL side you can grant SQL server access and nothing else.

Willy.

Well in regards to add the log in name to the SQL server I just did the
following, no problem.
exec sp_grantlogin 'MyDomain\MyCom puter$'

I had a feeling the domain thing was causing me problems. I am writing
and testing my code on a workstation so there is no second server at this
point. When the code is released it will be on a second server so my
current problem may be mute but it would be nice to test in as close to
the same environment as possible.

I am not using asp.net, this is a normal service which accesses the SQL
server on a second computer which happens to be a Win2K3 server.

Oh well thanks for the pointer, I guess I will read about shadow accounts
today :)

Regard,
John

John,

Sorry, don't know why I was refering to asp.net, anyway this applies to any
service running on windows.
That means it can be tested (the shadow account) with the file server
service as well.
Create the same account on both machines.
Grant access to a file share on the server.
Runas the "shadow account" on the client, and see you can access the files
on the remote share.

Willy.
Willy.

[John J. Hughes II]

"Willy Denoyette [MVP]" <wi************ *@telenet.be> wrote in message
news:uO******** ******@TK2MSFTN GP12.phx.gbl...

"John J. Hughes II" <[e-mail] > wrote in message
news:ew******** ******@TK2MSFTN GP12.phx.gbl...

"Willy Denoyette [MVP]" <wi************ *@telenet.be> wrote in message
news:ew******** ********@TK2MSF TNGP09.phx.gbl. ..

"John J. Hughes II" <[e-mail] > wrote in message
news:Oe******** ******@TK2MSFTN GP12.phx.gbl...
Again thank you for your support.

I have worked around the registry problem and added the computer name
to the SQL as a valid log in with access to the correct databases.

Now I have the following error message from the connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection."

My connection string is "Data Source=MyServer ;Initial
Catalog=DBData; Integrated Security=SSPI;P ersist Security
Info=False;Pack et Size=4096;Works tation ID=MyWks" which works fine from
the user account.

Is this message because my workstation is set as a workgroup and not
part of the Win2K3 domain or do I need to configure the connection
string differently?

Regards,
John

I'm unclear on how you were able to (successfully) add a remote machine
account to SQL. Machine accounts are W2K (or higher) domain members and
must be specified as domain\MachineN ame$ where domain is the domain
where both SQL server and client machine are members of. Each machine
account is just like a regular domain user account, so authentication
requests are handled by the DC of the login domain, and as you aren't
running in a domain realm this feature won't work for you.

If you need SQL integrated security to work in a workgroup, you should
create a shadow account on both servers that is an account with the same
identity and password. At the asp.net side you must grant the required
privileges to the local system resources like Filesystem and Registry,
on the SQL side you can grant SQL server access and nothing else.

Willy.

Well in regards to add the log in name to the SQL server I just did the
following, no problem.
exec sp_grantlogin 'MyDomain\MyCom puter$'

I had a feeling the domain thing was causing me problems. I am writing
and testing my code on a workstation so there is no second server at this
point. When the code is released it will be on a second server so my
current problem may be mute but it would be nice to test in as close to
the same environment as possible.

I am not using asp.net, this is a normal service which accesses the SQL
server on a second computer which happens to be a Win2K3 server.

Oh well thanks for the pointer, I guess I will read about shadow accounts
today :)

Regard,
John

John,

Sorry, don't know why I was refering to asp.net, anyway this applies to
any service running on windows.
That means it can be tested (the shadow account) with the file server
service as well.
Create the same account on both machines.
Grant access to a file share on the server.
Runas the "shadow account" on the client, and see you can access the files
on the remote share.

Willy.
Willy.

Willy,

How is this different then just going to properties of the service and
setting the "Log On" property to "This Account" and using a local account?

Currently if I change the log on account for this service to my user account
the system works fine. I am trying to test as a network service which seems
to have a whole bunch of other problems. The problem is when I run as a
network service then the user name is <null> when trying to log on to the
SQL server. Will this shadow account allow me to in some way convince the
network service it has a valid name?

Regards,
John