14 Replies Latest reply on Apr 2, 2010 8:24 AM by TSGal

    FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory

    T

      Summary

      FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory

      Description of the issue

      There has been no response to my message with the same subject posted on the forum (quoted below).  Does this mean that the feature doesn't work, or that it's so obvious no one wants to comment? - TomSituation:(1) FileMakerPro 10 (not server)(2) Active Directory(3) MS SQL Server(4) Trying to configure a datasource in FM using ODBC to connect to MS SQL Server.(5) Works fine when I specify a username and password(6) Does not work when I try to use Use Windows Authentication (Single Sign-0n)(7) In particular, SPN: MSSQLSvc/123.456.789.123:1433 does not work. (8) MS SQL is running under the built-in Network login, which is supposed to register an SPN automatically with Active Directory.(9) MS SQL has TCP connections enabled(10) I can't ask the Network Manager, because that's me.

        • 1. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
          TSGal

          T:

           

          Thank you for your post.

           

          FileMaker will send information to the ODBC driver.  What is done after that point is up to you.  It appears this is working properly for you with a username and password.  What evaluation is done in MS SQL for Windows Authentication?  I'm not sure what else can be done on the FileMaker end since the information is being sent to the driver successfully.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
            T

            Filemaker is sending the correct information to the ODBC driver when SQL Server authentication is being used .  It is sending the incorrect information to the ODBC driver when Windows (Single Sign On) authentication is being used.  What can be done on the FileMaker end is send the correct information to the ODBC driver.

             

            Do you know of any user who is able to use Windows authentication in FileMaker to access SQL Server?

             

            Please note that Windows authentication works just fine with the same ODBC driver when used by Microsoft Access, Excel, etc.  Are you asserting that Windows is to blame, since it can't handle FileMaker?

             

            "FileMaker will send information to the ODBC driver.  What is done after that point is up to you.  It appears this is working properly for you with a username and password.  What evaluation is done in MS SQL for Windows Authentication?  I'm not sure what else can be done on the FileMaker end since the information is being sent to the driver successfully."

            • 3. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
              T

              I should add that there is a big difference between using SQL Server authentication, in which case the user fills in all the information required for authentication, and Windows (Single Sign On) authentication, in which FileMaker has to be aware of the credentials used to log into the Windows account in which FileMaker is running.  Access, Excel, etc. have no trouble doing that.  FileMaker is evidently not able to do it at all, which suggests that FileMaker Windows Single Sign On simply doesn't work.

               

              - Tom

              • 4. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                TSGal

                T:

                 

                Are you capturing information as it reaches the ODBC driver?  Are you saying it is different when you send it to the MS SQL ODBC driver as opposed to the same driver with External Authentication?

                 

                I am not saying Windows is to blame?  If the same information is being sent to the ODBC driver, and one is able to interpret it correctly, but the other isn't, then I'm not sure what I can do.

                 

                Are you running MS SQL Server and FileMaker Pro on the same machine?  If so, then make sure the Windows account used to host  the file has "Impersonate a client after authentication" privilege enabled.  To do this, open Control Panel and go to Administrative Tools -> Local Security Policy.  In the Local Security Policy panel, open Local Policies -> User Rights Assignment.  Double-click on the privilege named "Impersonate a client after authentication" and add the Windows user account that intends to host the FileMaker Pro file to the list of users and groups.  You don't need to add any other privilege than that.

                 

                Does the account entered have full privileges or partial privileges?

                 

                Make sure the person is a member of the group that the account in FileMaker is mapped to.  There was an issue with one customer who had the default Admin account active without a password.  When the password was added, it corrected the problem.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                  T

                  I'm running SQL on a different machine.  FileMaker is not hosting -- sharing is turned off.  Windows is running under an account with full SQL privileges.

                   

                  I just want to make sure we're talking about the same issue.  Do you know if anyone has been able to connect to a MS SQL Server 2008 datasource via Windows ODBC using Windows Authentication?  If so, what string did that person type into the SPN: textbox on the FileMaker Pro "Edit Data Source" form?

                   

                  Thanks,

                   

                  Tom

                   

                   

                  • 6. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                    TSGal

                    T:

                     

                    After trying this myself, I was also not able to get this to work.  I finally received a reply from Development as follows:

                     

                    "This will not work with single-sign on since external authentication is a Server-only feature.  The prompt for the user name and password that appears is given to the SQLServer database to use for SQLServer-based authentication, so it will not work for Windows authentication."

                     

                    I have forwarded your entire post to our Development and Software Quality Assurance (Testing) department so they can consider changing this in a future release.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                      T

                      Hi TSGal,

                       

                      "This will not work with single-sign on since external authentication is a Server-only feature."

                       

                      Does this mean that if a FileMaker file is hosted on FileMaker Server, you can use Windows authentication for ODBC connections to MS SQL data sources?

                       

                      - Tom

                      • 8. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                        TSGal

                        T:

                         

                        Yes, that appears to be the case.  You can use Windows authentication with FileMaker Server, and then have the ODBC connection to MS SQL.

                         

                        TSGal

                        FileMaker, Inc.

                        • 9. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                          T

                           

                          Yes, that appears to be the case.  You can use Windows authentication with FileMaker Server, and then have the ODBC connection to MS SQL.

                           

                          TSGal

                          FileMaker, Inc.

                          • 10. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                            TSGal

                            T:

                             

                            Sorry for the misunderstanding.  I was under the impression from your original post that you were "Trying to configure a datasource in FM using ODBC to connect to MS SQL Server"; not trying to connect to FileMaker using MS SQL.  What commands are you sending from MS SQL to the FileMaker ODBC driver?  This may provide a clue.

                             

                            For more information on connecting external sources to FileMaker through ODBC, see the "ODBC and JDBC Guide" that came with the product, or you can view it online at:

                             

                            http://www.filemaker.com/downloads/pdf/fm10_odbc_jdbc_guide_en.pdf

                             

                            TSGal

                            FileMaker, Inc.

                            • 11. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                              T

                              I don't understand your response. 

                               

                              You said that FileMaker Server would allow me to connect to SQL datasources using Windows Authentication.  I asked you to tell me how.

                               

                              Here's the scenario:

                               

                              Machine A runs MS SQL Server.

                              Machine B runs FileMaker Server.

                              Machine C runs FileMaker Pro.

                               

                              I want to give FM Pro running on machine C access to a datasource on Machine A using Windows authentication.  You said that I can do that if I use FM Server.  But I see nothing in the FM Server documentation which explains how to set up an ODBC connection on Machine B which will enable FM Pro running on Machine C to access Machine A. 

                               

                              If this is not the case, please forward specific instructions on how to configure an ODBC connection on the FM Server machine such that clients accessing it remotely can use the FM Server's ODBC connection TO A SQL SERVER DATASOURCE.

                               

                              Thanks,

                               

                              Tom

                              • 12. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                                TSGal

                                T:

                                 

                                Sorry.  I guess I had trouble with the semantics of your post.

                                 

                                The ODBC driver that comes with FileMaker Pro/Servier is to allow other applications to use FileMaker as a data source.  In your case, you want to use Microsoft SQL Server as a data source, so that ODBC driver would come with Microsoft SQL; not FileMaker.

                                 

                                The best information I have on connecting to an SQL Server data source is in the Help.  Specifically,

                                 

                                http://www.filemaker.com/help/html/odbc_ess.20.7.html#1027863

                                 

                                This help topic is about Single Sign On but it does reference Microsoft SQL.  Assuming all of the steps were configured properly, let me know if any of the steps fail.

                                 

                                TSGal

                                FileMaker, Inc.

                                • 13. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                                  T

                                  Please note item (7) in my original message, quoted below.

                                   

                                  (1) FileMakerPro 10 (not server)

                                  (2) Active Directory

                                  (3) MS SQL Server

                                  (4) Trying to configure a datasource in FM using ODBC to connect to MS SQL Server.

                                  (5) Works fine when I specify a username and password

                                  (6) Does not work when I try to use Use Windows Authentication (Single Sign-0n)

                                  (7) In particular, SPN: MSSQLSvc/123.456.789.123:1433 does not work. 

                                  (8) MS SQL is running under the built-in Network login, which is supposed to register an SPN automatically with Active Directory.

                                  (9) MS SQL has TCP connections enabled

                                  (10) I can't ask the Network Manager, because that's me.

                                   

                                  You said that FileMaker Server would solve the problem.  When I asked you how, you referred me to http://www.filemaker.com/help/html/odbc_ess.20.7.html#1027863 , which pertains to FileMaker Pro, not FileMaker Server, and also provides an SPN which does not work, as I point out in item (7)!

                                   

                                  So please answer this question: are you aware of anyone who has been able to connect to MS SQL Server from any version of FileMaker using Windows authentication?  If so, what SPN did they use?  If not, why is that option provided on the data sources screen?

                                   

                                  - Tom

                                  • 14. Re: FM Pro 10 ODBC Connection to MS SQL Single Sign On via Active Directory
                                    TSGal

                                    T:

                                     

                                    I do not have access to Microsoft SQL Server.  I am going by previous customer inquiries and solutions.

                                     

                                    The link I sent you shows FileMaker Pro AND FileMaker Server.  It is relevant to FileMaker Pro.

                                     

                                    In one related customer case, I discovered that an issue was reported where Single Sign-On works fine if Microsoft SQL Server and FileMaker are running on the same machine, but fails on separate machines.  Here are the notes:

                                    ==============

                                    Single Sign-On does not work if the SQL Server service log on as "Local System" when SQL Server and FileMaker host are running on different machines.  Steps taken:

                                    1. Open SQL Server Configuration Manager and open SQL Server Service Properties dialog.

                                    2. Pick "Local System" for Log on property and restart SQL Server

                                    3. setspn -L (SQL Server machine) to verify SPN

                                    4. Enable "Trust this computer for delegation" for SQL Server, middle tier computer on Active Directory.

                                    5. Log on the client using domain account that not allowed to access to SQL Server

                                    6. Setup system DSN for SQL Server and enable Windows authentication.

                                    7. Create a file with text field and start sharing the file.

                                    8. Long on other client using domain account that allowed to access to SQL Server.

                                    9. Open remote file and open Manage Database dialog.

                                    10. Create a table alias and add ODBC data source with below options:

                                       DSN: System DSN created in step 6 above

                                       Use Windows Authentication (Single Sign-On): MSSQLSvc/sql.domain.com:1433

                                     

                                    This results in getting an incorrect login alert.  No problem this configuration with IIS.

                                    The Single Sing-On only works with the condition when the SQL Server service log on as domain user and the user has "Account is Trusted for Delegation" property.

                                     

                                    Here is the working condition:

                                    1. Open SQL Server Configuration Manager and open SQL Server Service Properties dialog.

                                    2. Use domain user for Log on property and restart SQL Server.

                                       setspn -L (domain user name) to verify SPN

                                    3. Enable "Account is Trusted for Delegation" for domain user on Active Directory.

                                     

                                    =====================

                                     

                                    This case was sent to Development and Testing, so they are aware of this issue.

                                     

                                    Let me know if this helps.

                                     

                                    TSGal

                                    FileMaker, Inc.