Install SharePoint 2010 with SQL Authentication

Hello,

last time i get a nice question on my desk and could not find any documentation for this. That’s the reason why i create this post.

Normally it’s not difficult to create a SQL authentication for one web application but for the whole farm we need to enter sql authentication settings in the psconfig wizard during farm creation. Unfortunately i couldn’t find a way  over the UI in sharepoint 2010 but it’s still possible and not difficult with using psconfig command shell.

 

1. go to the 14-Hive folder of SharePoint 2010, and start the help command for configdb

Psconfig.exe –help configdb

For SQL Authentication it’s important to define to parameter:

- -dbuser

- -dbpassword

2. create Configdb and FARM

a. here you can find this screenshot of my psconfig command to create the farm.

  

in a better readable version:

3. now the farm will be created, (only 3 important steps to create the farm, create configdb and Central Admin are done)

a.

4. Check:

a. SQL dbs on SQL Server

.

The Central Admin path is set automatically on Port: 20270 (in my case), but not deployed on IIS Server. (check IIS Manager and you will see no central admin is deployed, the Farm is also only created on WSS v4 level because the SharePoint Server Features are not installed & deployed)

To provision the central admin on IIS you can execute:  psconfig –cmd adminvs -provision

Now check your IIS and you will see the central admin web application:

Attention: who is farmadmin on my example?

In the screenshot above i was logged on as demo\administrator and the central admin was created from this account. With my first psconfig command i define another account to be farm administrator (demo\farmadmin). So demo\farmadmin is not the account who can access the central administration.

So that’s my fault, it’s always necessary to logon as farmadmin account to create the sharepoint farm and central administration

To access the central admin i need to use now demo\administrator

While browsing over the central admin you will see some missing features like: create service application, services to start etc. That’s okay because until now we only create WSS Farm.

Now we have two options to start the next 6 steps to complete the feature installation, deployment process  etc. to get a SharePoint Server Farm. Therefore we can start the next commands via psconfig…, but i don’t like that.

Let’s use the easiest way and start the PSconfigUI.exe (SharePoint 2010 Configuration Wizard). Because the farm is already created the wizards will reset the configuration for us and install all not installed features for us. Because the farm is already created i don’t lose sql authentication setting.

5. Run SharePoint 2010 Configuration Wizard:

Now we can see in central administration Server Feature like Service Applications are available:

6. Evidence that SQL Authentication is in use: start SQL Profiler Trace and check which access is in use:

It’s my SQL-authentication account: (sql_SharepointAccount)

regards

Patrick

Comments

• Anonymous
  January 01, 2003
  Thanks Patrick - I'll give this a shot and let you know how that goes.

• Anonymous
  January 01, 2003
  The comment has been removed

• Anonymous
  January 01, 2003
  Patrick, I'm on all smile now :-)  figured what the issue was (noted below for your readers). SQL authentication is up and running. Thank you for all your help! Resolution: Stupid SharePoint does not work well with SQL instance AND port number. So trick is don't include the port number on the connection string. Use like this: servername.test.globalinstance1 (NOT servername.test.globalinstance1, portnumber). Wired part was though I was able to install RTM SharePoint Foundation fine but SP1 did not like that.

• Anonymous
  January 01, 2003
  hello BlueSky2010, i'm not 100% sure if it hits your case but over the time i've seen that .net code in sharepoint 2010 creates .NET Remote Transactions to sql server e.g. my blog post to UPRE (blogs.technet.com/.../sharepoint-2010-user-profile-replication-engine-upre-troubleshooting.aspx) On Technet is written technet.microsoft.com/.../cc646023.aspx -> "Microsoft Distributed Transaction Coordinator (MS DTC)" ->  "TCP port 135" -> "If your application uses distributed transactions, you might have to configure the firewall to allow Microsoft Distributed Transaction Coordinator (MS DTC) traffic to flow between separate MS DTC instances, and between the MS DTC and resource managers such as SQL Server. We recommend that you use the preconfigured Distributed Transaction Coordinator rule group. When a single shared MS DTC is configured for the entire cluster in a separate resource group you should add sqlservr.exe as an exception to the firewall." This can be one reason why it fails.  Further statement require a detailed analyze. grap data, analyze, ... and a statement from Microsoft Support Group. Did you run a SQL Profiler Trace & Netmon trace? Compare the Good vs. Bad traces and i believe you will see the answer ;-) regards Patrick

• Anonymous
  January 01, 2003
  Hi Adam, i ever did my tests with adding service accounts manually on sql server and give them all required permission like: db owner, security admin. if your farm admin account has not enough permission on sql you can add other service accounts with your farm admin on sql server. what's the issue in detail? regards patrick

• Anonymous
  January 01, 2003
  hello @all, sorry for my late response, i have some idears which i need to validate first before i can share more. My basic idear would be: any spcontentdatabase & spdatabase has a string called: databaseconnectionstring. after creating a serviceapplication with a non-sql auth. database, we may have the change to modify / recalculate this string over the object model. this would be a workarround and i believe this idear is not the best idear and can cause side effect with provisiong mechanism. i believe it's not recommended from redmond. if i have some free time i'll try to do some test. if you need fast answer then please open a support ticket. regards patrick

• Anonymous
  January 01, 2003
  Hi Patrick, Great article - this is exactly what I was looking for. I've a farm installed with Windows authentication and now we need to switch it to SQL authentication. I'm seeing your steps are pretty straightforward creating the new farm with SQL authentication. Do you know whether I would be able to restore my existing farm on the SQL authenticated farm (and have all the configuration/ customization/data intact)? Current farm uses forms based authentication and SQL membership provider. Also some custom solutions deployed. Not sure what would happen with those. Any experience with these? I guess in worst case content databases can be just attached, right? Really appreciate any feedback! Thanks in advanced.

• Anonymous
  January 01, 2003
  The comment has been removed

• Anonymous
  September 10, 2010
  The comment has been removed

• Anonymous
  October 16, 2010
  I agree with Ryan, Patric do you have any experince that could be shared?

• Anonymous
  October 29, 2010
  Patric, As mentioned in two comments before me, is there any guidance from MS on how to setup SharePoint 2010 including all service applications using SQL Authentication? Sameer Dhoot | http://sharemypoint.in

• Anonymous
  January 18, 2011
  The comment has been removed

• Anonymous
  June 22, 2011
  This is a great article but I am having horrible issues with the Search Service Application, does anyone know how to create on in SP 2010 using Powershell, I admit I am savvy in stsadmn, not so much in  Powershell, any help would be appreciated. thanks

• Anonymous
  January 29, 2012
  The comment has been removed

• Anonymous
  November 10, 2014
  Thks for this tutorial !
  
  I still have a pbl: I cannot create a new Web Application: "Could not connect to using integrated security".
  I intend to create the Web App via PowerShell, but don't know the parameter in order to specify that SQL mode is used? An idea ?