Error connecting to SQL Server after changing Service Account to normal domain users

Description:

As security best practice, we should not run SQL Server Services with domain admin credential. However after changing it to the normal domain user credential, you encounter connection error message when trying to connect from SQL Server Management Studio.

The error may say something like "The target principal name is incorrect.  Cannot generate SSPI context."

Resolution:

We need to provide the appropriate permission for the domain user credential to modify ServicePrincipalName attribute in Active Directory.

• Run Adsiedit.msc
• In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
• In the CN= AccountName Properties dialog box, click the Security tab.
• On the Security tab, click Advanced.
• In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
• Click Edit, Open Permission Entry dialog box.
• Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
  • Read servicePrincipalName
  • Write servicePrincipalName
• Click OK to apply all changes and exit the ADSI Edit snap-in
• Restart the SQL Service(s) that use the account in question.

Error Connecting to SQL Server Instances after enabling Windows Firewall

Description:

For security reason, you need to enable the Windows Firewall on your SQL server machine.

However, after you enable them, user cannot connect to one of your SQL Instances. You already create Inbound TCP rule to allow port 1433 and another TCP port where the instance listened, but users still cannot connect. They can connect if the specified the port number for that instance was directly written on the connection page.

Resolution:

Make sure you also create inbound rule for UDP port 1434. The SQL Server browser service runs on UDP port 1434 and listens for incoming connections to a named instance.

How to Rename A Domain Controller

Description:

During Active Directory Upgrade, you might need to maintain the old Domain Controller name because of certain application requirement. You planned to use the swing method, where the new Domain Controller will be renamed to old Domain Controller name.

Resolution:

1. Make sure the old DC name is not being use anymore in the entire domain. Check Active Directory Object, check DNS Record, Check DFSR Object, etc.
2. Use Netdom command to rename the Domain Controller according to the following steps:
• On the new Domain Controller, open Command Prompt with Administrative Privileges
• Type netdom computername “current_name” /add:”fqdn_newname” > press enter
• Type netdom computername “current_name” /make primary:”fqdn_newname” > press enter
• Restart Domain Controller
• Type netdom computername “current_name” /remove:”fqdn_oldname” > press enter

Migrate SYSVOL from FRS to DFSR

Migration of SYSVOL from FRS to DFSR is actually quite simple.

First, you need to make sure all the Domain Controllers (DC) in the Domain are online and the AD replication and SYSVOL Replication are working normally.

After that, just need to run few of the following command in sequence:

1. At command Prompt, type "dfsrmig /setglobalstate 1". This is to enter the Prepared state.
• Type dfsrmig /getmigrationstate to confirm all domain controllers have reached prepared state
2. Next at Command Prompt, type "dfsrmig /setglobalstate 2". This is to enter the Redirected state.
• Type dfsrmig /getmigrationstate to confirm all domain controllers have reached prepared state
3. Next at command prompt, "type dfsrmig /setglobalstate 3". This is to enter the Emilinated state.
• Type dfsrmig /getmigrationstate to confirm all domain controllers have reached prepared state

Cannot Access Remote Share on Workgroup Computer using Local Admin Account

Description:

You have several servers in Workgroup environment. You know the Local Administrator Account and Password. You try to connect to the Administrative Share from Remote Workgroup machine but always failed. Network port for Ping, RPC, SMB from source to destination are all open. Username and password are both correct.

Resolution:

Open Regedit and go to "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System". 

1. On the Edit menu, point to New, and then click DWORD (32-bit) Value.

2. Type LocalAccountTokenFilterPolicy to name the new entry, and then press Enter.

3. Right-click LocalAccountTokenFilterPolicy, and then click Modify.

4. In the Value data box, type 1, and then click OK.

5. Exit Registry Editor.