We have successfully used the external data source option to connect to an MS SQL Database so we can setup regular Fetching of user enrollment Data. 

We plan on asking users 2 security questions when they need to unlock their account or reset their password. 

1. What is your date of birth (dd-mm-yyyyy)?

2. What year did you join the company (yyyy)?

Our development team has kindly setup a custom view in the database we are connecting to to pull enrollment data into ADSS Plus. This is called  ADSelfServicePlus.vwStaffDetail

The three columns were are interested in from this view are Login, DateofBirth and DateFirstJoined 

The Database itself does not contain the questions however I have discovered a command that will insert the question for each returned row of the query. I have not had much luck in pulling the data in and was wondering if someone would be able to have a look at our queries below and see if they are valid and will work with ADSS Plus?

Fetcher 1 Query - User ID, Question and answer

select distinct login, ('Please enter your Date of Birth (dd-mm-yyyy)'), dateofbirth from ADSelfServicePlus.vwStaffDetail where login is not null

This Returns the below data in SQL Server Management Studio (I have blacked out the usernames for security reasons).

Again Please note that the question itself does not exist in the databse, I am simply adding this in by using the command  ('Please enter your Date of Birth (dd-mm-yyyy)')

Fetcher 2 Query -  UserID, Question and answer

select distinct login, ('Please enter the year you joined the company (yyyy)'), left(DateFirstJoined, 4) from ADSelfServicePlus.vwStaffDetail where login is not null

This Returns the below data in SQL Server Management Studio (Again I have blacked out the usernames for security reasons).

Again Please note that the question itself does not exist in the database, I am simply adding this in by using the command  ('Please enter the year you joined the company (yyyy)')

Also one to note here is that the DateFirstJoined column by default shows the format yyyy-mm-dd so I have used the clause   left(DateFirstJoined, 4)  to show only the first 4 digits as this is the data we are interested in, year they joined in yyyy format. 

I have tested these in ADSS PLus and neither will successfully fetch the data as it shows in MSSQL Management Studio, perhaps the tool does not recognize this level of query or the arguments/clauses used?

Perhaps we need a new simpler database setup with just the username, question, answer columns already in the required format? 

Any help or advice on this one would be much appreciated. 

Best Regards