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

Question

Post reply

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

mfillion@controlperfect.com

SSC Enthusiast

Points: 114
More actions
February 26, 2020 at 9:09 pm

#3729087

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

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 2

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

mfillion@controlperfect.com SSC Enthusiast Points: 114 More actions · Answer 3

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 !

mfillion@controlperfect.com SSC Enthusiast Points: 114 More actions · Answer 4

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

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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