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

clip_image001

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.

   clip_image003

in a better readable version:

clip_image005

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

a. clip_image007

4. Check:

a. SQL dbs on SQL Server

. clip_image009

clip_image011

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)

clip_image013

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

clip_image015

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

clip_image017

clip_image019

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

clip_image021

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:

clip_image022clip_image023

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

clip_image025

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)

clip_image027clip_image029

regards

Patrick

Comments (15)

  1. Anonymous says:

    Thanks Patrick – I'll give this a shot and let you know how that goes.

  2. Anonymous says:

    Patrick,

    Do you know whether opening RPC port 135 to SQL a requirement for SQL authenticated SharePoint setup?

    I installed SharePoint foundation with SQL authentication – everything fine here. When I deployed service pack 1 and try to run the Configuration Wizard network guys are seeing a RPC call (port135) to the SQL server.

    I'm trying to justification on opening this port.  I'm finding this is a requirement to DC (AD) /DNS (LDAP).   – can you help?

    Many thanks!!!

  3. Anonymous says:

    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.

  4. Anonymous says:

    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

  5. Anonymous says:

    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

  6. Anonymous says:

    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

  7. Anonymous says:

    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.

  8. Anonymous says:

    Hi Patrick,

    Can you please confirm whether your SharePoint AD service accounts are added as SQL logins during the config db creation?

    In my experience the install adds the Farm account as a SQL login, which is causing me issues in a DMZ environment with SQL on the internal LAN.

    Thanks,

    Adam.

  9. Ryan Gallier says:

    How do you deal with the services that need access to the DBs? I have an environment where the MOSS box is in the DMZ on a DMZ domain and the SQL box is secure in the LAN and not on the domain.  I was able to create the farm just fine but it failed on a bunch of services.  "Secure Store Service", "Managed Metadata Service", "Web Analytics Service Application", "Application Registry Service", and "Search Service Application" all need access to make DBs.  The only way to configure those services is with a domain user.  How did you deal with these?  

  10. Jonas Deibe says:

    I agree with Ryan, Patric do you have any experince that could be shared?

  11. Sameer Dhoot says:

    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

  12. Patric, in refrence to Sameers question. I have looked everyware and other than setting up the farm, there seems to be no information on using SQL Auth when configuring service applications. Have you found or is there any guidance from MS on using SP2010 in a DMZ setup with only SQL Auth as the connection to the Databases? That is the way that I am forced to currently deploy. and Today with MOSS 2007 you can deploy that way.

  13. Danielle says:

    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

  14. Hello Buesky2010,

    as i understand you correct, your farm1 is installed and you want to switch the farm over restore options to farm2. farm2 was installed with sql auth..

    In my mind i did not see a problem. the database infrastructure structure design is in my mind a layer deeper located as the content structure.

    if sharepoint infrastructure in farm2 still runs, then each service application is a single component and if you were able to detach to reattach service application databases, then you are able to (re-)configure the authentication type of the database. each db is only the container and i would also expected (but i've not tested) that an restore from serviceApplication1 is possible into a new service Application database.

    I would expect a similar because like site collection backup/restore behavior, that means you can  move sitecollection over backup/restore over contentdb boundaries. it means the contentdb is only the container and the structured data (means content of the contentdb) is only backuped & restored.

    regards

    patrick

  15. charles says:

    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 ?

Skip to main content