ADFS 2.0 + SQL 2012

Question

Hello All,

First off can I thank you for taking the time to read this forum post. It should be a quick question to answer.

Does anyone know if SQL 2012 is a supported SQL server for ADFS 2.0?

Regards

Stephen

---

Stephen Davies

Answer 1

I have not heard any official forward compatibility support announced. As far as I know, ADFS in Windows Server 8 will probably support SQL 2012 since SQL 2012 is already out now and Windows Server 8 is not released for a while from now.

I would be interested if you tried it and it worked. If you use the farm setup the SQL scripts for setup are copied out for you. I would expect that it would probably work but I do not know of anyone who has tried it yet.

Thanks,

---

If this answers your question, please use the "Answer" button to say so | Ben Cline

Answer 2

I had issues installing AD FS 2.0 into SQL 2012, just installed into another SQL instance (2005) in the meantime.

Cheers
Gareth

Answer 3

Gareth,

  Thanks for replying! I am not the moderator of this forum, but your reply should be marked the answer since I have not tried to install ADFS with this configuration.

Ben

---

If this answers your question, please use the "Answer" button to say so | Ben Cline

Answer 4

Hi Gareth,

I ended up just upgrading the CRM SQL server and leaving the ADFS SQL server alone until we hear word that ADFS 2.0 will be supported by SQL2012.

Thanks

---

Stephen Davies

Answer 5

There is a DEFINITE compatibility issue with ADFS2.0 and SQL2012, the internal scripts use RAISERROR in a fashion that is no longer supported, so they need to be rewritten to work in SQL2012, but our testing hasn't born out a given set of changes to say if it even works long term (past the configuration steps, which we have not even been able to complete thus far...).

-SparkDustJoe

Answer 6

Has anyone tried AD FS included Windows 2012 and SQL 2012?

Have an AD FS cluster, want to put the database on in a SQL 2012 AwaysON availability group.

Answer 7

I can confirm it works now in Windows 2012, together with SQL 2012. Had to run this:

fsconfig.exe CreateSQLFarm /ServiceAccount DOMAIN\companyadfs-s /SQLConnectionString "Database=AdfsConfiguration;server=SQLCLUST01.DOMAIN.co.nz;integrated security=SSPI" /FederationServiceName auth.company.co.nz /CertThumbPrint "b0 41 41 a2 7b 4b e4 55 31 34 22 22 19 59 dd 7d 51 41 00 8c" /SigningCertThumbprint "12 d3 68 a2 33 4b 33 18 f1 21 14 22 19 62 dd 7d 51 f8 00 8c" /DecryptCertThumbprint "12 d3 68 a2 33 4b 33 18 f1 21 14 22 19 62 dd 7d 51 f8 00 8c" 

Where SQLCLUST01.domain.co.nz points to the listener on the availability group.

Then just ran fsconfig.exe on the other AD FS servers to join the farm with joinsqlfarm.

Better get your knives and forks, this is about to get tasty :)

Now to get CRM 2011 to play nicely.

Answer 8

It's not working in my environment.

I have error:

Failed: An error occurred during an attempt to perform the configuration task: I
ncorrect syntax near '50000'.
Changed database context to 'AdfsConfiguration'.

Win2k8 r2 + ADFS 2.0 + SQL 2012 Standard

Any ideas?

Answer 9

"Win2k8 r2 + ADFS 2.0 + SQL 2012 Standard"

We've done it but haven't fully tested it end-to-end.  The script templates in the ADFS installation folder have to be edited (or replaced) to remove references to RAISERR (replace them with THROW).  RAISERR was deprecated in SQL2012 and thus every instance causes the error. 

I don't have a working copy of the script template handy to put here, and it's rather large to begin with, so it won't fit in this thread anyways.

We also tried renaming the database name in the scripts, but later calls to the DB elsewhere fail, it ABSOLUTELY wants the DB name to be a certain value, so don't change that.  You can ONLY have ONE ADFS DB in a given instance of SQL.

We tried to come up with an automation script for configuring ADFS, including a version that attempts to work in SQL2012.  It seems to work pretty well but we haven't released it into the wild yet.

@SparkDustJoe on twitter

http://www.liquidmercurysolutions.com

Answer 10

I've tried easier way, I mean install ADFS 2.0 + SQL Express 2008 R2, then move db to sql2012. Like in http://social.technet.microsoft.com/wiki/contents/articles/948.ad-fs-2-0-migrate-your-ad-fs-configuration-database-to-sql-server.aspx 

But ADFS cannot connect to AdfsArtifactStore:

Set-ADFSProperties : Exception of type 'Microsoft.IdentityServer.PolicyModel.Client.StorageOperationException' was thrown.

It looks like ADFS cannot use this DB on SQL2012.

Answer 11

I was wondering if anything has changed in the migration story now?

Is recreation of all of the ADFS environment the only way to move to SQL 2012?

I am dealing with an upgrade to SQL 2012 now and was wondering if there was any easier ways that work.

---

If this answers your question, please use the "Answer" button to say so | Ben Cline

Answer 12

We have a working proof of concept of ADFS 2.0 in SQL 2012 for SharePoint 2010.   This removes the need to go all the way to ADFS 3.0, which I don't think SharePoint 2010 supports at all.

As I posted earlier, the work-around requires some script editing, and a hefty amount of PowerShell, but we have a nearly fully automated process, end-to-end.

Reach out to us at Liquid Mercury Solutions, I can't post it openly as we've built it into a business process, but we are certainly willing to help others with their production environments.

http://www.liquidmercurysolutions.com

-SparkDustJoe

Answer 13

I ended up recreating my ADFS environment. I had created some scripts and tools to deploy everything with ADFS 2.0 so redeploying it to 2.1 was not that bad.

Here is a blog post on the changes I encountered while upgrading: http://msinnovations.wordpress.com/2012/11/08/changes-in-adfs-2-1-from-adfs-2-0/

Thanks,

---

If this answers your question, please use the "Answer" button to say so | Ben Cline

Answer 14

All,

I have a script that is now working in SQL 2012 with ADFS 2.0. It is fully tested and everything looks to be ok.

Ping me a message if you want it.

Adam - IAM Cloud

Answer 15

Hi AC1985

I'm having the "Incorrect syntax near '50000' error with ADFS2.0+SQL2012.

Could you share the script?

Thank you

Answer 16

I would be interested in that script, Adam.

Answer 17

This is a heads up, we were using the script we rolled into a version of AutoSPInstaller we developed, and after installing a service pack, the stored procedures were edited and the RAISEERR command errors crept back in.   If you do CU's, you may have to go in and update those stored procedures again.  This is only for ADFS 2.x in SQL 2012.

Answer 18

Adam - Could you forward a copy of that script?  I would really appreciate it.

Brian

Answer 19

Hi all,

I made the needed changes to "StoredProceduresCreate.sql" file (by default located in C:\Program Files\Active Directory Federation Services 2.0\Sql\).

I just changed each RAISERR 50000 ''Some descriptive text'' to RAISERR(''Some descriptive text'',10,1). I'm not sure how I should provide this fix to the community, any suggestions?

BR,

Tomi