ADFS SQL Server instance

Hi guys,
I hope you are all well and can assist.
We are looking at setting up our ADFS environment, and wish to use SQL Server 2008 as our adfs database.
This will be on 2008 R2 SQL Server, and we wish to create a new sql instance on this server for adfs.
What I would like to know is the following:
1) What are the requirements in terms of SQL instance configuration adfs requires?
eg.collation set etc
2) Any other help greatly appreciated in determining what, from a sql database point of view, are the configuration things we need to make sure get done for adfs to work.

An example below I got online is this:

FSConfig.exe CreateSQLFarm /ServiceAccount pipe2text\adfslabuser /ServiceAccountPassword Password /SQLConnectionString “database=AdfsConfiguration;server=sqlservername\instance;integrated security=SSPI” /CleanConfig /FederationServiceName /AutoCertRolloverEnabled

What I dont understand is the following:

Does the SQL instance have to be created first?
Im assuming it does, and if this is the case, what collation set etc is needed?

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

x-menIT super heroCommented:
The SQL Server instance has to be intalled/created first. You can lead it with the default collation, since it will only afect tempdb, The ADFS database will be created with it's own collation, by the deployment.

further reading:

SQL Server considerations
You should consider the following deployment facts if you select SQL Server as the configuration database for your AD FS deployment.
SAML features and their effect on database size and growth. When either the SAML artifact resolution or SAML token replay detection features are enabled, AD FS stores information in the SQL Server configuration database for each AD FS token that is issued. The growth of the SQL Server database as a result of this activity is not considered to be significant, and it depends on the configured token replay retention period. Each artifact record has a size of approximately 30 kilobytes (KB).

Number of servers required for your deployment. You will need to add at least one additional server (to the total number of servers required to deploy your AD FS infrastructure) that will act as a dedicated host of the SQL Server instance. If you plan to use failover clustering or mirroring to provide fault tolerance and scalability for the SQL Server configuration database, a minimum of two SQL servers is required.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simon336697Author Commented:
Hi xmen,
I cannot thank you enough. Could I please ask for clarification on the below..

1) On the server named "SERVERA", which has sql server installed on it, create the sql server instance that the adfs server will connect to, for example:


2) Build a new 2012 server and install ADFS on it. Use a domain admin account to do this. Server name is "SERVERX". This is the first ADFS server in the environment and is thus the Primary Federation Server.


This will just install the service, but not ask for the location/type//creation of the adfs database. Is this correct?

3) Configure the Primary ADFS server "SERVERX".

This is where I really need clarification.

fsconfig.exe (this is needed to be used instead of the configuration wizard since the configuration wizard does not give you the option to use a sql server database)

FSConfig.exe CreateSQLFarm /ServiceAccount pipe2text\adfslabuser /ServiceAccountPassword Password /SQLConnectionString “database=AdfsConfiguration;server=sqlservername\instance;integrated security=SSPI” /CleanConfig /FederationServiceName /AutoCertRolloverEnabled

Questions about the above:

q) Am I correct when I say that when you use adfssetup.exe, that an adfs database is not created?

q) The server "SERVERA" is the sql server, which already hosts a number of sql instances for other applications.  I obviously want to ensure that when i run fsconfig.exe and point to this server SERVERA to use an instance that is already created, that other instances are not in any way tampered with or changed. Is this the case?

q) I am not exactly sure what the following does:

FSConfig.exe CreateSQLFarm

Is this just specific to adfs, and will not impact any other instances on SERVERA?

q) When using fsconfig.exe, is the database created?

database=AdfsConfiguration (this is in the previously mentioned string)

q) I am assuming that it is, but only when fsconfig.exe is used with the CreateSQLFarm string?

q) If you add additional adfs servers to the farm, and want it to not create a different sql database, but point to the same db that the primary sql server is using, is this where you use the following:

FSConfig.exe JoinSQLFarmb
and specify the database to use?

q) How does the access work when first setting up the adfs farm?

As part of the fsconfig.exe, there is a /ServiceAccount switch which you need to specify an account.
Is this the account that is used to create the adfs database?
Does this account need to be added to the Security logons on the sql server for the sql server instance and granted permissions such as account DB Creator and Security Admin in SQL. Will this user then have the rights to create the adfs database?

q) When you run the command fsconfig.exe to set up the sql from the adfs server, is it the account you are running the fsconfig.exe with that creates the adfs sql database, or is it the account you specify after the /ServiceAccount switch?

q) I believe there is also certificate involvement required as part of the configuration, but I dont know why and what their purpose is for. All i know is that there is the following switch:

I am really sorry for all the questions.
x-menIT super heroCommented:
I bellive all your questions are answered on the following articles. I giving you the articles instead because there's a lot more info about Federation Services and diferent "checklist" for diferent "partner federation".

Setting Up a Federation Server:

Install the AD FS 2.0 Software:

Create the First Federation Server in a Federation Server Farm:
Simon336697Author Commented:
Thanks so much!
That is brilliant x-men.
Simon336697Author Commented:
Thank you champion !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.