NT AUTHORITY\ANONYMOUS 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\ANONYMOUS 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\NetworkService" 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\ComputerB$" (dollar sign is important).
Just add this account to SqlServer and you should be able to connect.

 

[Willy Denoyette [MVP]]

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\NetworkService" 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\ComputerB$" (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]

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\NetworkService."

 

[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;Persist Security Info=False;Packet
Size=4096;Workstation 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

 

[Willy Denoyette [MVP]]

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;Persist Security Info=False;Packet
Size=4096;Workstation 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\MachineName$ 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]

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\MachineName$ 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\MyComputer$'

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]]

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

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]

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

 

[oj]

When you create the same NT account (let's call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you're being authenticated via the station2's account. So, if you set the
startup account for SQL Server (let's assume it's running on station2) to be
station2's usr1, when you connect to SQL from station1 with station1's usr1
login, SQL will authenticate you as station2's usr1.

Now, within SQL, you can definitely access station1's resources. Though, how
much access will depend on station1's usr1 permission.

So far, we only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network resources,
you will have to set the proxy account. Take a look at the article for
additional info.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Also, be aware that SP3 has added a new flag. You would have to set it for
your environment.

e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
go

If you have any SQL jobs that access network resources, you would want to
set SQLAgent startup account to the same one for SQL Server.

 

[Willy Denoyette [MVP]]

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

Service accounts like "Network Service", "Local Service" and "Local
System", use the "Machine account" credentials when authenticating over the
network, but as I said before this only works when your Machine is a member
of a W2K/W2K3 AD domain.
In a non AD realm, you have to run your service with the shadow account's
credentials, when challenged by the remote SQL server, the credentials are
passed (encrypted) to the SQL server box where they get validated against
the local security database, which holds the exact same credentials, so
authentication suceeds and the client (your service) is authorized to access
the SQL server service. Note that I'm explicitely stating SQL Server,
accessing the DB resources (tables, SP etc..) is a matter of SQL DB access
control.

Willy.

 

[Willy Denoyette [MVP]]

Service accounts like "Network Service", "Local Service" and "Local
System", use the "Machine account" credentials when authenticating over
the

Small correction, "Local System" authenticates as "anonymous" over the
network.

Willy.

 

[John J. Hughes II]

Thanks,
John

When you create the same NT account (let's call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you're being authenticated via the station2's account. So, if you set the
startup account for SQL Server (let's assume it's running on station2) to
be station2's usr1, when you connect to SQL from station1 with station1's
usr1 login, SQL will authenticate you as station2's usr1.

Now, within SQL, you can definitely access station1's resources. Though,
how much access will depend on station1's usr1 permission.

So far, we only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network
resources, you will have to set the proxy account. Take a look at the
article for additional info.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Also, be aware that SP3 has added a new flag. You would have to set it for
your environment.

e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
go

If you have any SQL jobs that access network resources, you would want to
set SQLAgent startup account to the same one for SQL Server.

 

[John J. Hughes II]

Thanks,
John

Small correction, "Local System" authenticates as "anonymous" over the
network.

Willy.