Ever encountered this issue? Been working with Sitecore for the past years and up until now I can still – sometimes – encounter this problem. So, this time, I decided to create a post wherein I could document the steps on how I resolved the issue, and hopefully for those who might run into this issue, as well.
Issue: Login failed for user ‘DOMAIN\USER-1$’.
The issue caused by a task whereby I have to transfer PROD SC instance to UAT environment and that I have to change the sources in the connectionstrings, thus the issue aroused.
There are two (2) approaches to fix the problem.
- Change your connection strings – \Website\App_Config\ConnectionStrings.config
- Add the ‘DOMAIN\USER-1$’ to your SQL Management studio > Login account
We will start on the easiest route.
- Change your connection strings – Basically, the reason why you’re getting the above issue is because you don’t have or your machine doesn’t enough permission to access the database. Change the connectionstrings with the following:
<add name="core" connectionString="Data Source=10.2.3.456,1433;Database=scProjectX_Core.upgrade-beta.20160807;Integrated Security=true"> <add name="master" connectionString="Data Source=10.2.3.456,1433;Database=scProjectX_Master.upgrade-beta.20160807;Integrated Security=true"> <add name="web" connectionString="Data Source=10.2.3.457,1433;Database=scProjectX_Web.upgrade-beta.20160807;Integrated Security=true">
Important note: When the Integrated Security is set to TRUE, the current windows account credentials are used for authentication. In this case, the server name.
<add name="core" connectionString="user id=sclivecd;password=mgl@cunA!;Data Source=10.2.3.456;Database=scProjectX_Core.upgrade-beta.20160807"> <add name="master" connectionString="user id=sclivecd;password=mgl@cunA!;Data Source=10.2.3.456;Database=scProjectX_Master.upgrade-beta.20160807"> <add name="web" connectionString="user id=sclivecd;password=mgl@cunA!;Data Source=10.2.3.457;Database=scProjectX_Web.upgrade-beta.20160807">
Important note: When the Integrated Security is set to FALSE, the User ID and Password are specified.
Tips: Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.
If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used. More about different types of SQL connectionstring here.
2. Add the ‘DOMAIN\USER-1$’ to your SQL Management studio > Login account – This is simply adding a permission for the server name whom accessing the database server, and to be able to still use the Integrated Security. Steps on “How to create domain\user in MS SQL Management Studio“.
2.1 Once the DOMAIN\User has been created. Follow these steps on “How to create a custom application pool identity in IIS using windows DOMAIN\User“
Today, I had a chance to talk with our Senior Solution Architect via Skype about checking the connection between UAT environment and Production database server.
The goal is just to check if it’s working, as we’re planning to create a mimic of Production CM and CD in the UAT environment using the production database server
Note that this concept is also applicable to the rest of the environments,
- SIT > UAT
- UAT > SIT
- SIT > PROD
- PROD > SIT
A very high-level representational diagram:
So, to check, Open the command prompt and start typing the below command.
telnet 10.1.2.345 1433
Important note: 1433 is the default TCP port of the SQL Server default instance. This is the common port allowed through the firewall. It applies to routine connections to the default installation of the Database Engine, or named instance that is the only instance running on the computer. More about configuring the Windows Firewall to Allow SQL server access here.
You would be able to see this window if successful connection.
For failed connection, you would encountered a Connection failed message.
In this post, I will show you how to create domain\user account for your Sitecore instance in SQL Management Studio.
Open the SQL Management Studio, and go to Security > Logins, right-click and the New Login…
In this screenshot, I’ve already added the domain user. “TECL\SCSTGCD”
In the General tab, specify the Login name, in my case “TECL\SCSTGCD”. You can also change the Default database and the Default language on the bottom part of the window.
In the Server Roles tab, specify the permission of the domain user,
Note: Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.
The sysadmin, on the other hand, is a fixed server role that can perform any operation or activity in the server. More info about Server-Level roles here.
In the User Mapping tab, explicitly specify by checking the mapping of the user for the databases and its default schema.
In the Securables tab, make sure that the Domain user have the ‘GRANT’ permission to connect to SQL, by default the Grantor is SA
In the Status tab, double check if the domain user ‘Grant’ radio button is selected and Login Enabled.
Then click the OKAY button, and there you go.