Re: proper permissions for aspnetdb membership access

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It was taking too long to fix this. So I decided to abandon it. After dealing with a 'can't restart' bug, I uninstalled SQL 2008, and reinstalled it from scratch. I've started a new thread to try to get very basic login working. I hope we can continue there.


"JRL" <jrl@xxxxxxxxxxxxx> wrote in message news:up4Cbk8hJHA.6128@xxxxxxxxxxxxxxxxxxxxxxx
Hi Charles,

thanks for working through this patiently with me. It's a learning experience for me.

From your connection string, the login account was the SQL login view,
however the error message was for NT AUTHORITY\NETWORK SERVICE which is a
Windows built-in account.

Isn't this because the login services to aspnetdb do this by default? I can't see anywhere in my code that specifies any operation to open aspnetdb, so I assume it is part of the login control.


1. Open your SQL Server Management Studio (SSMS) with a local
administrator, create a login named NT AUTHORITY\NETWORK SERVICE from
windows, give it db_datareader and db_datawriter permission on your
database and then directly run the following statement to grant EXECUTE
permission to the NETWORK SERVICE user:
GRANT EXECUTE ON OBJECT::aspnet_CheckSchemaVersion To [NT AUTHORITY\NETWORK
SERVICE];

I had some problems with this first instruction. In the windows 'create user account' control panel, I couldn't create a user by the name of NT AUTHORITY\NETWORK SERVICE (it would only allow up to the end of NETWORK so maybe the name is too long. In any case, since this is a built in account, I questioned why I would create a user account. Isn't it already available? It exists as an account in SMS.
So, even though I couldn't create a windows account by that name, I was still able to execute the query.

After trying to login now, the error is:
Exception message: The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'aspnetdb', schema 'dbo'.


2. Open IIS, right click the Application pool that your web application
used, click Properties, switch to the Identity tab, change the account to a
domain user account which has the fixed server role sysadmin in your SQL
Server instance. If it does not exist in the logins of your SQL Server
instance, please manually add it in SSMS.

For this second instruction, I might need clarification. In IIS, I can't find where to get to the properties of the Application pool which my application uses. In IIS, I can see the Application Pools item in the Connections Panel. Selecting it, I see the list of applications, and I see my application has the identity NetworkService. By clicking advanced settings, I could change the identity from NetworkService to another account, but the list of built in accounts is limited to Local System and Local Service. This is probably not what you intended. Please clarify where I have misunderstood your direction.

Also, since my goal here is to understand the permissions I am using with this account, would you agree with the following understanding of the situation: NetworkService is the application identity, so it can utilize the NT AUTHORITY\NETWORK SERVICE account, as defined in SMS. A second account (now referred to as view, but was collector) can access the second database. The login operations which aren't yet succeeding, are having problems because some of the operations (which I can't actually view it seems) are using impersonation. For some reason (possibly that I imported this user from a SQL 2005 server), the impersonation isn't working.

One approach would be to explicitly give permissions as needed for the login. So I tried GRANT EXECUTE ON OBJECT::aspnet_Membership_GetPasswordWithFormat To [NT AUTHORITY\NETWORK
SERVICE]; This was the next blocked operation, but afterward, I still couldn't log in. The error is still: Exception message: The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'aspnetdb', schema 'dbo'.

The second approach would be to reset the objects to their default condition so that impersonation would work. Is this possible?

I'll follow up on any instructions you suggest.

.

Relevant Pages

  • Re: SQL 2k5 SP2 Mirroring - SQL in Mixed mode.
    ... US\sqlservices (old SQLservice account) still member of built-in local admin ... Login: US\sqlservices ... How can I transfer the SQL logins to ... Here is SP_help_revlogin results from the Principal Server (NYSQL-3) ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL Server on XP Home Network
    ... The sa account is the system administrator "God" account witihin SQL ... This account is a SQL-Server login. ... You might be able to use Enterprise Manager to go in and create a new SQL ... Select the "SQL Server Authentication" ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Connection with .udl and domain accounts
    ... work because it's not recognised by SQL-Server as a valid User Id. ... difference between a SQL account and a domain account. ... Login account using a SQL Login name and password that will ...
    (microsoft.public.sqlserver.connect)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... If a SQL account is used then the only thing needed on the ... machine running SQL is SQL login and database grants. ... If integrated, then in addition, I have seen the account need ... then changing this to use trusted connection ...
    (microsoft.public.win32.programmer.wmi)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... If a SQL account is used then the only thing needed on the ... machine running SQL is SQL login and database grants. ... If integrated, then in addition, I have seen the account need ... then changing this to use trusted connection ...
    (microsoft.public.windows.server.security)