SQL
[Fixed] Login failed user DOMAIN\User. Cause and the solution.
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:
From:
<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“
Creating a Domain\User in SQL Management Studio for your #Sitecore instance
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.
Next: How to create a custom application pool identity in IIS using windows DOMAIN\User
How to delete the Sitecore WFFM Form Summary Report
So today, I wanted to delete all my test form summary reports.
I remember that during the WFFM installation, there was a post installation step, a prerequisite, before to start working with the WFFM.
Execute the WFFM_Analytics.sql
The script can be found in the \Website\Data folder. And when you open the .sql on your favorite editor. You’ll notice that it creates four (4) tables named:
- [dbo].[Fact_FormSummary]
- [dbo].[Fact_FormEvents]
- [dbo].[Fact_FormStatisticsByContact]
- [dbo].[FormFieldValues]
The submission of the data from a form will be inserted to these fields. So basically, if you want to delete the record, just run the sql scripts below in your SQL server:
-- DROP CONSTRAINT
ALTER TABLE [dbo].[Fact_FormSummary] DROP CONSTRAINT "FK_FormSummary_FieldValueId"
-- TRUNCATE TABLE
TRUNCATE TABLE [dbo].[FormFieldValues]
TRUNCATE TABLE [dbo].[Fact_FormSummary]
TRUNCATE TABLE [dbo].[Fact_FormStatisticsByContact]
TRUNCATE TABLE [dbo].[Fact_FormEvents]
-- RECREATE CONSTRAINT
ALTER TABLE [dbo].[Fact_FormSummary]
ADD CONSTRAINT [FK_FormSummary_FieldValueId] FOREIGN KEY ([FieldValueId]) REFERENCES [dbo].[FormFieldValues] ([FieldValueId])
Alternatively, if you want to completely erase the forms table then you can try this:
-- DROP CONSTRAINT
ALTER TABLE [dbo].[Fact_FormSummary] DROP CONSTRAINT "FK_FormSummary_FieldValueId"
-- DROP TABLE
DROP TABLE [dbo].[FormFieldValues]
DROP TABLE [dbo].[Fact_FormSummary]
DROP TABLE [dbo].[Fact_FormStatisticsByContact]
DROP TABLE [dbo].[Fact_FormEvents]
DROP PROCEDURE Add_FormStatisticsByContact
Download the .sql file here.
