In this article we are going to discuss a few important things we should know about installing SharePoint 2010 Farm using SQL Authentication & its limitations.
About SQL Authentication:
Basically there are 2-ways to connect to the SQL Server Management Studio, one using Windows & the other using SQL Authentication. For Windows obviously you need to have a domain Username & password and the same needs to be added under 'Security' --> Logins on the SQL Server Management Studio
To create a SQL login account on the SQL Server, login to the SQL Server Management Studio --> Security --> Logins, right click on 'Logins' and click 'New Login'
Now enter the Username of your choice and choose the radio button for 'SQL Server Authentication'
After the account has been created successfully, you can see the same account under Security --> Logins
To confirm if the account has been appropriate permissions, login to the SQL Server Management Studio using SQL Authentication & confirm your access.
Using SQL Authentication on SharePoint 2010:
Usually Organizations prefer to build SharePoint Farms using SQL Authentication when they have SQL & SharePoint Servers in two different domains and these domains usually do not trust between them, in such cases SharePoint Servers are most likely in DMZ Zone.
Let's first see how to start building a SharePoint Farm from the scratch using SQL Auth.
In this example we have 2-Domains: DOM233960A & DOM233960B which belong to SQL & SharePoint respectively. We do not have any kind of trust between these 2-domains.
Both the servers are on Win-2008 R2 SP1, I'm making use of SQL 2008 R2.
To begin with we need to setup a SQL Account on the SQL Server which has at least DBCreator & Security Admin role assigned.
In this example I have created an account by name 'sql' on the SQL Server as shown above (this account is on the SQL Server and absolutely not required to be created on the Windows Server itself)
Once the account is setup, login to the SQL Server Management Studio with the newly created account and confirm the login.
This being done, let's now to go the SharePoint Server.
Remember since we are building SharePoint Farm using SQL Auth, we do not have an option to run the SharePoint Configuration Wizard to setup the farm, we should be doing this via the command prompt.
Before we move on create a config DB, let's check the connectivity from SharePoint to SQL.
Create an UDL connection on the SharePoint Server - create a text document on the desktop of the SharePoint Server by name 'test.udl'
Double click on 'test.udl', enter the SQL Server name, SQL Account username & password created on the SQL Server, click on the drop down on step-3 which says 'Select the database on the server' as shown below as we should be able to see the DBs present on the SQL Server.
and when we hit 'Test Connection', we should get a response as ' Test connection succeeded'
Do not move forward if the UDL Test fails, Check the below points if the UDL Connection does not succeed.
Try to Telnet to the SQL Server from SharePoint, install Telnet Client (if not installed) from the Features menu.
Type the following command on the command prompt: telnet "name of the SQL Server" "port number"
In my case command looks like this: telnet sykh23396018 1433
If Telnet is successful, you will get the output as blank as shown below:
The port number 1433 is the default port which is used by SQL Server for remote connections to the database. If you are using any other port but 1433 make sure you have the 'SQL Server Browser' service running on the SQL Server so that you can telnet from the SharePoint server using the non-default port.
If Telnet fails, there are high chances it is blocked by the Firewall, if you are using Windows Firewall, either Disable the firewall or Add an Inbound Rule for the port 1433
Try the Telnet again, if successful, try the UDL. Once the UDL Test is passed be sure enough we have required communication setup between SQL & SharePoint.
It's time now for us to get to the Magical PSConfig Command which will create a new SharePoint Config DB & Admin Content DB, as usual we have to run this under the 14 hive folder on the SharePoint Server.
Please check TechNet article for detailed list of commands- http://technet.microsoft.com/en-us/library/cc263093.aspx
The required command is:
psconfig.exe -cmd configdb -create -server <Server_name> -database <Database_name> -dbuser <username> -dbpassword <password> -user <DOMAIN\username> -password <password> -admincontentdatabase <Database_name>
Note: '-user' will be the username which we become the Farm Admin after we run the above command.
'-dbuser' will be the 'sql' account which we initially created on the SQL Server.
Once the above command has been run successfully, it implies we have connected the SharePoint to the SQL Server using the SQL Authentication.
You can double check on the below registry which is the 'dsn' key, this 'dsn' key will only be created when SharePoint server is connected to a farm
Got to the SharePoint Server " HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\configdb"
And look for the 'dsn' key
In the above key if you notice, the 'Integrated Security=False' which confirms the SharePoint Server is connected to SQL using SQL Auth, & also the password is seen in clear text.
When you build a SharePoint Farm using Windows Auth, the same 'dsn' key will be seen as below:
Here if you see, the 'Integrated Security=True' which means this one is Windows Auth.
Now you can run the SharePoint Configuration Wizard via the UI and provision Central Admin and complete the wizard successfully.
(We can also provision Central Admin and run the wizard using command line)
This completes building of SharePoint 2010 farm using SQL Authentication.
Coming to the limitations of using SQL Authentication compared to Windows:
- Changing SQL Account Password:
At any given point in time if you are in a need to changing the SQL Account password, please bare in mind this is no joke.
There is no easy way to change the SQL Account password known to mankind as of now. If you happen to change the password accidentally on SQL Server for the SQL Account which was used to build the SharePoint Farm, as soon as the password is changed all the SharePoint sites will go down. You have to either go back and change the password again to the old one or build a new Config DB and associate all the Content DBs which is as good as rebuilding the complete SharePoint Farm.
Then how do I change the password?
- There is an option of Changing the password via the Object Model, however the success rate is minimal.
- There could be a hotfix rolled out in the near future, even otherwise changing SQL Account password is not recommended unless you are ready to re-build the farm.
- Do all functionalities of SharePoint work on a farm build using SQL Authentication?
Microsoft is trying their best to make work all the functionalities of SharePoint 2010 work as compared to Windows, however there are few things you must know. I ran the Farm Configuration Wizard after I built the SharePoint 2010 farm using SQL Authentication and found the following Services not to be working, below is the screenshot.
When I ran the Farm Configuration Wizard I was on RTM build which is 14.0.4762
- After I applied SP1 for SharePoint 2010, I again ran the Farm Configuration Wizard to provision the above 3-Service Applications, & I was able to provision "Search Service Application" & "Managed Metadata Service" however not the “Secure Store Service”
- I then had to install http://support.microsoft.com/default.aspx?scid=kb;EN-US;2536599 to get the "Secure Store Service" working
(Note: I have seen Secure Store Service working after installing June-CU-11 for SharePoint 2010 without having to install the above KB, if you want to stick only with SP1 then KB-2536599 is mandatory for "Secure Store Service")
- User Profile Synchronization Services DOES NOT work with SQL Authentication. You will be able to provision User Profile Service Application, however when you try to start the User Profile Sync Service, you will not be able to start, it will hang on starting & finally die. On the event viewer you will witness an error something like shown below:
Log Name: Application
Source: ILM Web Service Configuration
Date: 7/19/2011 9:02:28 PM
Event ID: 234
Task Category: None
ILM Database could not be created: Error sent to Windows Event Log running : EXEC sp_grantlogin N'DOMB\Administrator';DECLARE @existingUser nvarchar(128); IF EXISTS( SELECT name from sys.sysusers where sid=0x01050000000000051500000049B2CE8F62788E2A6A2DC5D4F4010000 )BEGIN SELECT @existingUser=name from sys.sysusers where sid=0x01050000000000051500000049B2CE8F62788E2A6A2DC5D4F4010000;IF( @existingUser <> N'dbo' )
EXEC sp_addrolemember N'db_owner', @existingUser;
EXEC sp_grantdbaccess N'DOMB\Administrator'; EXEC sp_addrolemember N'db_owner',N'DOMB\Administrator';END: Windows NT user or group 'DOMB\Administrator' not found. Check the name again.
Windows NT user or group 'DOMB\Administrator' not found. Check the name again.
User or role 'DOMB\Administrator' does not exist in this database.
User Profile Sync Service may work with SQL Authentication in future, as of now even on latest builds it does not.
There could be more limitations on building SharePoint 2010 farm using SQL Auth other than mentioned above and that's the very reason why Microsoft prefers & recommends Windows Authentication over to SQL when building SharePoint farms.
*******Updated on 22-Mar-2013*******
*Installing Oct-CU-2012 for SharePoint Server 2010 helps to start User Profile Sync Service even on SharePoint 2010 Farms built using SQL Authentication. The fix was included for this issue was included in Oct-CU-2012 and above Cumulative Updates for SharePoint Server 2010
This update also allows us to change the SQL Account Password
“Assume that you create a new User Profile Service Application (UPA), and you configure the synchronous database to use SQL authentication by setting up a SharePoint farm as an administrator. In this situation, the UPA creation is successful, but the UPA synchronization service cannot start”