locked
SQL server 2012 Login failed RRS feed

  • Question

  • Hi

    I am using SQL server 2012 Web Edition on Windows 8 Pro with IIS8. When I open my website through IIS8, I get a failed Login error for user NT AUTHORITY/SYSTEM.

    But my website runs fine when I open it using Development Server included with Visual Studio 2012 Professional and there are no login errors.

    What am I missing?

    Thanks

    Wednesday, October 31, 2012 10:53 AM

Answers

  • I will still recommend to use different identity and not system accounts - Have you changed settings in Anonymous authentication to use app pool identity?. See below if you want to grant permission to NT Authority / System user.

    USE [master]
    GO
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    USE [DB Name]
    GO
    CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]
    GO
    ALTER USER [NT AUTHORITY\SYSTEM] WITH DEFAULT_SCHEMA=[DBO]
    GO
    EXEC sp_addrolemember N'db_owner', N'NT AUTHORITY\SYSTEM'
    GO
    Cheers, Vishal Patel

    • Marked as answer by nice_newbee Wednesday, October 31, 2012 12:03 PM
    Wednesday, October 31, 2012 11:25 AM

All replies

  • Hello

    Your development server is using trusted authentication and IIS is using app pool identity to connect SQL server. Please change Application pool identity to different user (you might have to change anonymous authentication to App pool identity user) or alternatively grant permissions to NT Authority / System user on database.

    Cheers, Vishal Patel 

    Wednesday, October 31, 2012 11:02 AM
  • Hi Vishal Patel, thanks for the reply.

    I have tried different identities for DefaultApppool like NetworkService, LocalSystem etc but no luck. How to grant permissions to NT Authority / System user on database?

    Please help, I am stuck.

    Thanks again.

    Wednesday, October 31, 2012 11:13 AM
  • I will still recommend to use different identity and not system accounts - Have you changed settings in Anonymous authentication to use app pool identity?. See below if you want to grant permission to NT Authority / System user.

    USE [master]
    GO
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    USE [DB Name]
    GO
    CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]
    GO
    ALTER USER [NT AUTHORITY\SYSTEM] WITH DEFAULT_SCHEMA=[DBO]
    GO
    EXEC sp_addrolemember N'db_owner', N'NT AUTHORITY\SYSTEM'
    GO
    Cheers, Vishal Patel

    • Marked as answer by nice_newbee Wednesday, October 31, 2012 12:03 PM
    Wednesday, October 31, 2012 11:25 AM
  • Hi Vishal Patel, thanks for quick the reply.

    Have you changed settings in Anonymous authentication to use app pool identity?.

    No I do not know how to do that.

    Can you guide me?

    Thanks again.

    Wednesday, October 31, 2012 11:34 AM
  • Hi Visal Patel

    As regards your SQL commands, the user NT AUTHORITY#SYSTEM already exists so I removed CREATE LOGIN and CREATE USER lines. Replaced the [DB Name] with my database and the query executed successfully but it still does not work. Did I miss anything?

    Thanks

    Wednesday, October 31, 2012 11:45 AM
  • Hi Visal Patel

    Thank you, Thank you so much. You're cute.

    Ever since I installed Windows 8 on the 26th (the day I purchased it) and then tried to set up my Dev Env (VS 2012, IIS8 and SQL Server 2012) I was struggling to get it going. First I could not connect to SQL Server (the dreadful Server not Found or does not exists error). After having fixed that I ran into this problem. Since I am new to all this, had to struggle all the time.

    So after five long days you have made it possible and I cannot stress that enough.

    What I did now is that I have changed Anonymous Authentication to use Application Pool Identity and executed your SQL on my Databases and voila!.

    Perfect.

    Thanks again.

    Wednesday, October 31, 2012 12:03 PM
  • Connecting to SQL Server from IIS requires specific setup in IIS.

    This document is old, but the steps are still the same:

    http://msdn.microsoft.com/en-us/library/ff647396.aspx

    There is also a Database Manager in IIS

    http://www.iis.net/learn/extensions/database-manager/use-the-database-manager


    • Edited by Tom Phillips Wednesday, October 31, 2012 1:43 PM
    Wednesday, October 31, 2012 1:41 PM