CREATE LOGIN FROM Windows (AD account): Login failed for user

  • Hi,

    I have made with Server 2019 an VirtualBox test environment on my Workstation. I have :

    Active Directory Controller

    SQL Principal

    SQL Failover

    Web 01

    Web 02

    I develop an automate application that do this :

    Installation :

    Create Active Directory Windows Account

    Create Database on Principal with Failover

    Install Web Application on Web Servers

    Uninstallation:

    Remove Web Application from Web Servers

    Remove Database from SQL Servers

    Remove Active Directory Windows Account

    When I install for the first time with my automate application, the Windows Account "cp\Sample" is created and used for SQL and Application Pool. All working like a charm. When I uninstall and reinstall, a problem occurs, the Web Application said "Login failed for user cp\Sample".

    The automate use TSQL to create the LOGIN :

    USE MASTER;

    CREATE LOGIN [CP\SAMPLE]

    FROM WINDOWS

    WITH DEFAULT DATABASE = TEST;

    Afer I create USER :

    CREATE USER [CP\SAMPLE] FOR LOGIN [CP\SAMPLE]

    ALTER ROLE [db_owner] ADD MEMBER [CP\SAMPLE]

    I must delete cp\Sample from the SQL server and recreate it manually in SSMS and after that it's working well again.

    My application create the Windows Account like that :

    var user = new UserPrincipal(ctx, username, password, true);

    user.Name = name;

    user.DisplayName = name;

    user.SamAccountName = username;

    user.SetPassword(password);

    user.UserPrincipalName = username + "@" + domain;

    user.PasswordNeverExpires = true;

    user.UserCannotChangePassword = true;

    user.Save();

    Can any one already encountered problem like that ?

    Thank you very much !!

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Without the specific error from the SQL Server log when you get the login failure it's hard to say. I would check the logs for the details of the login failure. It's not real clear (to me anyway) what the process is with the uninstall and reinstall, if you remove databases, if you remove logins or recreate logins or just remove users or what happens with all of this. But finding the details in the SQL Server log related to the login failure with the full description and the state would likely point things to where the problem lies.

    Sue

  •  

    Hi,

    There is the log :

     

    02/27/2020 16:20:25,Logon,Unknown,Login failed for user 'CP\LeeNCo'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.1.10]

    02/27/2020 16:20:25,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 5.

     

    In detail there are the step that our software go through :

     

    Installation :

    1.ADC01 : Create a Windows Account in Active Directory

    2.SQL01 : Restore a database from a backup (template database) and naming it with the name of customer. The database restore has no reference for the Windows Account.

    3.SQL01 : Create Certificate for TDE and encrypt database

    4.SQL02 : Copy certificate

    5.SQL01 : Create backup for the new database that will be used for the mirroring

    6.SQL02 : Restore the database for the mirroring

    7.SQL01/SQL02 : Start mirroring

    8.SQL01 : Add the Windows Account to the SQL server and add a user to the database. The TSQL is :

    CREATE LOGIN [CP\LeeNCo]

    FROM WINDOWS;

    CREATE USER [CP\LeeNCo] FOR LOGIN [CP\LeeNCo];

    ALTER ROLE [db_owner] ADD MEMBER [CP\LeeNCo];

    9.SQL02 : Add the Windows Account to the server by using TSQL :

    CREATE LOGIN [CP\LeeNCo]

    FROM WINDOWS;

    9.Web Servers : install Web Application that use Application Pool running under the Windows Account [CP\LeeNCo] created in ADC01

     

    This procedure work fine the first time. But if we uninstall by the following steps and install again, the new Windows Account [CP\LeeNCo] stop working. There are the step to uninstall :

     

    1. Web Servers : uninstall Web Application by stopping all Application Pools before.

    2.SQL01 : Remove mirroring

    3.SQL02 : Delete database

    4.SQL02 : Remove Windows Account from SQL Server

    5.SQL02 : Remove Database certificate

    6.SQL01 : Remove Database

    7.SQL01 : Remove Database certificate

    8.SQL01 : Remove Windows Account from SQL Server

    9.ADC01 : Remove Windows Account [CP\LeeNCo]

     

    At this point, if we install again LeeNCo, the new windows credential use by the Application Pools on the Web Servers will have a SQL error like shown at the beginning of this email. The only way to correct the problem is by SSMS remove the [CP\LeeNCo] from the database and the server and recreate it. After that, the Web Application start working again.

     

    Thank you !

  • Ok,

    I resolve my problem. Windows Server has a cache for Windows Account. I have disabled the cache and problem is resolved.

    There is what I do to disable the cache :

    https://support.microsoft.com/en-us/help/946358/the-lsalookupsids-function-may-return-the-old-user-name-instead-of-the

  • Thanks for posting back - that makes sense. Another option would be to just skip the part of deleting the account from Active Directory when you do the uninstall and leave the cached logins settings as the default. Having the cached logins can sometimes be useful.

    Sue

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply