Access Denied Problem

Thanks for the response. Before I got any reply to my question, I decided
to place a service call with MS - they did a very good job of resolving the
problem.

The heart of the issue was the upgrade process from svr 2000 to svr 2003 and
the way it migrated changes from IIS5 to IIS6. The "network service"
identity did not have the right permissions to allow the web reference to be
set. Actually, there were other problems related to this issue.

As it stands, I have a work-around by using "local system" - which is not
the best solution. Except for SQLSvr, I've got all the other permissions
working correctly to use "network service". However, for SQL Svr, I can't
figure out how to grant permission to "network service" as it does not seem
to be a normal user that pops up in my list of domain users.

As I'm just working on a development network, this is not a critical issue -
but, I'd like to know how to fix the problem completely just for my
understanding. Any idea how to grant "network service" permissions for my
SQL Svr databases?

Thanks


"Yan-Hong Huang[MSFT]" <yhhuang DeleteThis @online.microsoft.com> wrote in message
news:LqWgM794DHA.568@cpmsftngxa07.phx.gbl...
 > Hello Dave,
 >
 > Thanks for posting in the group.
 >
 > Based on my understanding, now the issue is: You are developing web
service
 > and its client in asp.net, IIS 6.0, Win Server 2003 based on "Visual
Studio
 > Net Walk through - Chapter 2 Distributed Applications". Now the web
 > service can be created successfully. However, when you add web reference
in
 > a windows form client application, you got access failure error. Please
 > correct me if I have misunderstood the problem.
 >
 > I tested it on my side in the same box. However, I couldn't meet the
 > problem on my side. My web service is created in the local Win2003 server.
 >
 > Could you please provide the following information so that I could better
 > know the problem?
 >
 > 1) Which walk through are you working on now? Please paste the web link of
 > that walk through here. I will follow that to see if I can repro the
 > problem.
 >
 > 2) Please paste the exact error message here.
 >
 > 3) Have you turned off Anonymous access authentication for the Web service
 > application? If so, we need to provide network credential
programmatically.
 > For details, please refer to
<font color=purple> > <a style='text-decoration: underline;' href="http://support.microsoft.com/default.aspx?scid=kb;en-us;811318.</font" target="_blank">http://support.microsoft.com/default.aspx?scid=kb;en-us;811318.</font</a>>
 >
 > 4) Is there any web proxy related? Why do you need username and password
to
 > access that web service?
 >
 > If you have any more concerns on it, pleaes feel free to post here. I look
 > forward to your response.
 >
 > Best regards,
 > Yanhong Huang
 > Microsoft Community Support
 >
<font color=purple> > Get Secure! ¨C <a style='text-decoration: underline;' href="http://www.microsoft.com/security</font" target="_blank">www.microsoft.com/security</font</a>>
 > This posting is provided "AS IS" with no warranties, and confers no
rights.
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Access Denied Problem 

Hello Dave,

I am glad to hear that most of the problem has been resolved in that
service request. For the new question, how to assign right to Network
Service account in SQL Server, I am glad to explain it more here.

In fact, this is a FAQ in accessing sql server in asp.net programming.
Please refer to this article:
"PRB: "Login Failed" Error Message When You Create a Trusted Data
Connection from ASP.NET to SQL Server"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316989

From the article, we can see that we need to add Network Service account to
SQL server in IIS 6.0. The secret here is that Network Service account is a
hidden account, like local system, local machine. We can't see it in
Computer Management->Local Users and Groups. Please refer to the follow
steps:

1) In SQL Server Enterprise managed, create a new database named
TestDatabase.
2) In the users of that TestDatabase, right click and select "New Database
User...".
3) In the pop up dialog, click the downarrow of Login name group box, we
can see one account named "NT AUTHORITY\NETWORK SERVICE". I believe that is
what we want.

By default, the ASP.NET worker process on a Windows Server 2003 (IIS6)
machine uses the local "NT AUTHORITY\NETWORK SERVICE" user account. And on
Windows 2000 / XP (IIS 5.x), it uses the local ASPNET user account. Also,
ASP.NET has impersonnation turned off by default.

Hence, in this configuration, if you want your web page to access a Sql
Server database, you'll need to give database access permissions to the
corresponding
local user account, depending on which IIS version you are using.

Carl Prothman posted some following samples before. I pasted it here for
your reference:
------------------------------------------------------------

Here is a summary for a machine called "CARLP7" on IIS 5.x, with
Authentication mode="Windows" in web.config

- If Identity impersonate="false" and IIS Anon Access enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\ASPNET

- If Identity impersonate="false" and IIS NTLM enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\ASPNET

- If Identity impersonate="true" and IIS Anon Access enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\IUSR_CARLP7

- If Identity impersonate="true" and IIS NTLM enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\Administrator (logged on user)

Here is a summary for a machine called "CARLP7" on IIS 6.0, with
Authentication mode="Windows" in web.config

- If Identity impersonate="false" and IIS Anon Access enabled
=> WindowsIdentity.GetCurrent().Name = NT AUTHORITY\NETWORK SERVICE

- If Identity impersonate="false" and IIS NTLM enabled
=> WindowsIdentity.GetCurrent().Name = NT AUTHORITY\NETWORK SERVICE

- If Identity impersonate="true" and IIS Anon Access enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\IUSR_CARLP7

- If Identity impersonate="true" and IIS NTLM enabled
=> WindowsIdentity.GetCurrent().Name = CARLP7\Administrator (logged on user)

---------------------------

Does that answer your question?

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 >> Stay informed about: Access Denied Problem 

Thanks for the response.

I'm not able to find the "NT AUTHORITY\NETWORK SERVICE" in the dropdown list
of the pop-up dialog box when trying to add a new database user for
TestDatabase. So, I'm still not able to proceed.

I also have a stand alone server where I formatted the disk and did a fresh
install of Server 2003 and VS.NET 2003 and all service packs to SQL Server
2000. I have not encountered these same issues on that machine. I followed
the same procedure for the TestDatabase and did not see the network service
user there either.

Any more thoughts on finding this critter?


"Yan-Hong Huang[MSFT]" <yhhuang.RemoveThis@online.microsoft.com> wrote in message
news:QbpLtzU5DHA.568@cpmsftngxa07.phx.gbl...
 > Hello Dave,
 >
 > I am glad to hear that most of the problem has been resolved in that
 > service request. For the new question, how to assign right to Network
 > Service account in SQL Server, I am glad to explain it more here.
 >
 > In fact, this is a FAQ in accessing sql server in asp.net programming.
 > Please refer to this article:
 > "PRB: "Login Failed" Error Message When You Create a Trusted Data
 > Connection from ASP.NET to SQL Server"
<font color=purple> > <a style='text-decoration: underline;' href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;316989</font" target="_blank">http://support.microsoft.com/default.aspx?scid=kb;EN-US;316989</font</a>>
 >
 > From the article, we can see that we need to add Network Service account
to
 > SQL server in IIS 6.0. The secret here is that Network Service account is
a
 > hidden account, like local system, local machine. We can't see it in
 > Computer Management->Local Users and Groups. Please refer to the follow
 > steps:
 >
 > 1) In SQL Server Enterprise managed, create a new database named
 > TestDatabase.
 > 2) In the users of that TestDatabase, right click and select "New Database
 > User...".
 > 3) In the pop up dialog, click the downarrow of Login name group box, we
 > can see one account named "NT AUTHORITY\NETWORK SERVICE". I believe that
is
 > what we want.
 >
 > By default, the ASP.NET worker process on a Windows Server 2003 (IIS6)
 > machine uses the local "NT AUTHORITY\NETWORK SERVICE" user account. And on
 > Windows 2000 / XP (IIS 5.x), it uses the local ASPNET user account. Also,
 > ASP.NET has impersonnation turned off by default.
 >
 > Hence, in this configuration, if you want your web page to access a Sql
 > Server database, you'll need to give database access permissions to the
 > corresponding
 > local user account, depending on which IIS version you are using.
 >
 > Carl Prothman posted some following samples before. I pasted it here for
 > your reference:
 > ------------------------------------------------------------
 >
 > Here is a summary for a machine called "CARLP7" on IIS 5.x, with
 > Authentication mode="Windows" in web.config
 >
 > - If Identity impersonate="false" and IIS Anon Access enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\ASPNET
 >
 > - If Identity impersonate="false" and IIS NTLM enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\ASPNET
 >
 > - If Identity impersonate="true" and IIS Anon Access enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\IUSR_CARLP7
 >
 > - If Identity impersonate="true" and IIS NTLM enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\Administrator (logged on
user)
 >
 > Here is a summary for a machine called "CARLP7" on IIS 6.0, with
 > Authentication mode="Windows" in web.config
 >
 > - If Identity impersonate="false" and IIS Anon Access enabled
 > => WindowsIdentity.GetCurrent().Name = NT AUTHORITY\NETWORK SERVICE
 >
 > - If Identity impersonate="false" and IIS NTLM enabled
 > => WindowsIdentity.GetCurrent().Name = NT AUTHORITY\NETWORK SERVICE
 >
 > - If Identity impersonate="true" and IIS Anon Access enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\IUSR_CARLP7
 >
 > - If Identity impersonate="true" and IIS NTLM enabled
 > => WindowsIdentity.GetCurrent().Name = CARLP7\Administrator (logged on
user)
 >
 > ---------------------------
 >
 > Does that answer your question?
 >
 > Best regards,
 > Yanhong Huang
 > Microsoft Community Support
 >
<font color=purple> > Get Secure! ¨C <a style='text-decoration: underline;' href="http://www.microsoft.com/security</font" target="_blank">www.microsoft.com/security</font</a>>
 > This posting is provided "AS IS" with no warranties, and confers no
rights.
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Access Denied Problem 

Hello Dave,

Thanks for the quick response.

In order to find it, you can do the following steps:

1) In the dropdown list of the pop-up dialog box when trying to add a new
database user, click <new>.
2) In the Name editbox of the pop up dialog, click ...... button
3) Now we can see a dialog "SQL Server Login Properties - New Login", In
the combobox of "List Names From", select your local machine name.
4) Then we can see a IIS_WPG group. Click "Members" button and we can find
"NETWORK SERVICE" account.
5) Add this account and go ahead.

If there is anything unclear, please feel free to post here.

Have a good day.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 >> Stay informed about: Access Denied Problem 

I wish I could report success, but.... the procedure you detail works just
as you describe until it gets to the point of clicking the "members"
button - there is no NETWORK SERVICE listed, just IWAM_myservername is
listed.

You mentioned that should I select my local machine - the only thing that
shows in that dropdown is my domain name and one cannot force an entry -
must select from the drop down list and that list only shows the domain
name, and not the name of my local server machine.

I know that NETWORK SERVICE is on the machine somewhere because the guy from
MS support had me enter this is in some dialog box (don't remember where)
and we did a "name check" successfully. We also went on to use that user
name successfully. My SQL Server is on this same machine, so it is quite a
puzzle why we can't see NETWORK SERVICE in any of its dialogs.

Any more thoughts?

"Yan-Hong Huang[MSFT]" <yhhuang.DeleteThis@online.microsoft.com> wrote in message
news:sOUzpOh5DHA.824@cpmsftngxa07.phx.gbl...
 > Hello Dave,
 >
 > Thanks for the quick response.
 >
 > In order to find it, you can do the following steps:
 >
 > 1) In the dropdown list of the pop-up dialog box when trying to add a new
 > database user, click <new>.
 > 2) In the Name editbox of the pop up dialog, click ...... button
 > 3) Now we can see a dialog "SQL Server Login Properties - New Login", In
 > the combobox of "List Names From", select your local machine name.
 > 4) Then we can see a IIS_WPG group. Click "Members" button and we can find
 > "NETWORK SERVICE" account.
 > 5) Add this account and go ahead.
 >
 > If there is anything unclear, please feel free to post here.
 >
 > Have a good day.
 >
 > Best regards,
 > Yanhong Huang
 > Microsoft Community Support
 >
<font color=purple> > Get Secure! ¨C <a style='text-decoration: underline;' href="http://www.microsoft.com/security</font" target="_blank">www.microsoft.com/security</font</a>>
 > This posting is provided "AS IS" with no warranties, and confers no
rights.
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Access Denied Problem