12 Replies Latest reply on Feb 25, 2014 3:00 PM by LSNOVER

    DSN Connection to MS SQL

    msawyer

      Hi

       

      Can either the ExecuteSQL function or Execute SQL script step be used to pass a user name and password to a DSN connection with MS SQL.

       

      I cannot get the ESS connection to stop prompting for a user name and password. It has been set up correctly in the FM file and the DSN is connecting and tested.

       

      Server is 2008 R2 64 bit, FMS 12.05 and FMS13 clients.

       

      If possible would appreciate some guidance

       

      Regards

       

      Matt

        • 1. Re: DSN Connection to MS SQL
          PSI

          Matt,

           

          Did you use the 32 bit ODBC connection? It's accessed from Windows/SysWOW64/odbcad32

           

          John

          • 2. Re: DSN Connection to MS SQL
            msawyer

            Hi John

             

            Familiar with the 32 bit set up on 64 bit machines.

             

            Its a 64bit Server and 64 bit install of FM Server and it will only see the 64 bit DSN.

             

            Seeing the DSN  and connecting is not the issue. The problem is that it asks for credentials the first time a user needs to access the data from the ESS connection very day. All credentials are in place in External Data Sources.

             

            Thats why I want to see if there is another way of passing the credentials.

             

            Thanks

             

            Matt

            **********************************************

            Matt Sawyer

            Information for Business

            Tel: +61418 160059 Phone: 61 2 9328 2492

            • 3. Re: DSN Connection to MS SQL
              user19752

              Execute SQL script step is executed on specified ODBC data source, ESS has nothing to do with it.

               

              Only FMS13 server side script may use 64ibt ODBC. All FMPro runs on 32bit,

              and FMS12 doesn't support Execute SQL on server side.

               

              To resolve connection problem, let's see SQL Server log.

              • 4. Re: DSN Connection to MS SQL
                msawyer

                Hi

                 

                SQL is on a remote server managed by a third party.

                 

                All the FM application is doing is importing data from an ESS table set up in FileMaker.

                 

                I guess your suggesting the issue might be something to do with the remote setting requiring verification, even though this is set up in the DSN and in External Data Sources.

                 

                Will see if I can get hold of it.

                 

                Thanks

                 

                Matt

                 

                 

                **********************************************

                Matt Sawyer

                Information for Business

                Tel: +61418 160059 Phone: 61 2 9328 2492

                • 5. Re: DSN Connection to MS SQL
                  user19752

                  Sorry Matt, my last reply had written before reading your 21:28 post.

                   

                  It seems authentication settings is in ESS, that in DSN is not used.

                  So testing on DSN is not enough for using in FM ESS.

                   

                  Probably you use SQL Server Authentication...

                  http://msdn.microsoft.com/en-us/library/ms144284.aspx

                   

                  and prompting mean that used user/password may be invalid.

                  • 6. Re: DSN Connection to MS SQL
                    monkeybreadsoftware

                    You could use MBS FileMaker to connect to MS SQL database and send SQL commands.

                    If you have questions, email me. Or check the exampels on our website.

                     

                    Greetings

                    Christian


                    • 7. Re: DSN Connection to MS SQL
                      msawyer

                      Thanks.

                       

                      The user enters the same User name and password used in the DSN each day.

                       

                      Not to worry. Will have a chat to the provider to see if they have a setting on their side.

                       

                       

                      **********************************************

                      Matt Sawyer

                      Information for Business

                      Tel: +61418 160059 Phone: 61 2 9328 2492

                      • 8. Re: DSN Connection to MS SQL
                        LSNOVER

                        I think we need to understand what this prompt is and when and how it is being generated.

                         

                        Connecting via the Execute SQL Script step or a plug in MAY get around the prompt, but it really depends what it is from.

                        • 9. Re: DSN Connection to MS SQL
                          msawyer

                          Happens when hoping to layout displaying ESS data

                           

                          Sent from my iPhone

                          Matt Sawyer

                          Information for Business

                          • 10. Re: DSN Connection to MS SQL
                            LSNOVER

                            Matt:

                             

                            Does it happen only once per day per user?  Do you use a general login for the ESS Database, or are your logins user specific?   If you login with another tool first (like Excel), do you get the same extra login?  If you do, do you still get the extra login in Filemaker after logging in with another app?

                             

                            Regards,

                            Lee

                            • 11. Re: DSN Connection to MS SQL
                              msawyer

                              Hi Lee

                               

                              Standard ODBC log-on request.

                               

                              Had already checked; Excel 2013 cannot see 64 bit DSN's and FileMaker cannot see 32 bit DSN's.

                               

                              Thanks for your help Lee, I am going to check that the service provider hasn't got a setting that forces the log-on.

                              • 12. Re: DSN Connection to MS SQL
                                LSNOVER

                                Marc:

                                 

                                If it's based on the account and it's a one time a day prompt, it should not matter which driver you are going through.