8 Replies Latest reply: Mar 24, 2015 5:32 AM by Rafal Antosik RSS

    OLE DB SQL Native Client connection

      Hi all,

       

      I'm facing a problem connecting to MS SQL Server 2008 R2. In Qlik 11.20.11922.0 R2 x64 I'm trying to set up an OLE DB connection through "SQL server Native client" (v10 and v11), since they are offer a higher performance than "Microsoft OLEDB provider for SQL Server".

      When configuring my connection I set up the server name, the user name and its password and Database Name, I complete *successfully* the test Connection procedure. Notice that the user name I use for the connection is different from the User Account I use to connect to my computer (running Qlik).

      When I press OK, I expect that some code will be written on my script page but I recieve an error message claiming that "ErrorSource: Microsoft SQL Server Native Client 10.0, ErrorMsg: Login failed for user '<MyDomain\My_Computer_User>'". Notice that, as I said before, my computer user name is different from the user provided for the connection AND that Test connection is successfull!

      Any guess?

       

      Thanks in advance

      Andrea

        • Re: OLE DB SQL Native Client connection

          Update: the user used to connect to SQL Server is a DB user, neither a local server user nor a domain user,

          Update 2: no success even forcing 32 bit connection

            • Re: OLE DB SQL Native Client connection

              Try to insert the connect statement using Edit Script --> Insert --> Connect Statement.

              On the dialog box, Connection Tab, for item(2) "Enter information to log on to the server", select the radio button for "use a specific username and password" and check "Allow saving password".

              Test Connection and if successful, click OK.

              You should see a connection statement included in your script. If not, look for a open window asking for database credentials again. It might be hidden behind the "Edit Script" window. I have seen this behaviour with Oracle connections, but it may be true for SQL Server too. Unfortunately our SQL Server db is setup for integrated windows security, and I am unable to check this out myself.

              If neither the connect statement is inserted in the script nor there is a open window, try to insert again.

              Once the connect statement is inserted in the script, you should be able to connect to the SQL server, If not, then investigate on the db server why its failing. It may not be Qlikview issue.

              • Re: OLE DB SQL Native Client connection

                Try to insert the connect statement using Edit Script --> Insert --> Connect Statement.

                On the dialog box, Connection Tab, for item(2) "Enter information to log on to the server", select the radio button for "use a specific username and password" and check "Allow saving password".

                Test Connection and if successful, click OK.

                You should see a connection statement included in your script. If not, look for a open window asking for database credentials again. It might be hidden behind the "Edit Script" window. I have seen this behaviour with Oracle connections, but it may be true for SQL Server too. Unfortunately our SQL Server db is setup for integrated windows security, and I am unable to check this out myself.

                If neither the connect statement is inserted in the script nor there is a open window, try to insert again.

                Once the connect statement is inserted in the script, you should be able to connect to the SQL server, If not, then investigate on the db server why its failing. It may not be Qlikview issue.

                  • Re: OLE DB SQL Native Client connection

                    Thankyou smqlikview for your reply. I was following exactly the steps you mentioned in your post. I click the Connect button, then I specify the driver add, on the secon tab I compile all the fileds: server name, username, password, uncheck blank password, check Allow to save password and Test connection is successful. I've even checked the All properties tab and I see that are properly compiled. When I press ok, the invalid user name error message appears, no chance to insert a new username or password. See the picture below, connection is Ok!

                    QlikConnectionSuccessfull.png

                    ...And this is the error I get after I press Ok. I left unhidden the '\' to highlight that Qlik is using my Domain credentials, instead of a DB user name. I use DB username in different contexts, so I don't think that the problem is outside Qlik..

                    QlikErrorConnection.png

                    So I think I'm acting correctly. What is wrong?

                    Regards

                      • Re: OLE DB SQL Native Client connection
                        Kevin Swindlehurst

                        Andrea,

                        I'm having the exact same problem (old post, new user ). I've narrowed down the actual issue a bit more, and I don't think we're doing anything wrong. I think it's a bug:

                        This connection builder utility is not converting the "Use a specific user name and password:" selection into the correct initialization property value. Look on the "All" or "Tutte le proprieta" tab, the problem property is Integrated Security. When Windows NT is chosen, Integrated Security's value is "SSPI", and that works fine. When Specific User Name and Password is selected, Integrated Security's value is blank\empty string. Blank isn't a valid value for the connection builder, so the connection builder tries to use Windows NT by default. That's why you see your NT credentials in the error message. People that have both WindowsNT access and user+pwd credentials for the database don't get the error because their NT credentials are valid.

                         

                        There was just one server here that I didn't have an NT account for and everyone else did. It was driving me crazy.

                         

                        We can work around this by changing the Integrated Security property value right before hitting OK using the [Edit Value...] button. Changing the value to "false" or using the [Reset Value] button both work for me. The resulting OLEDB connection omits the Integrated Security property completely. If someone else builds a connection with "Integrated Security ="";" included, you'll have to delete that text before you can reload.

                          • Re: OLE DB SQL Native Client connection
                            Rafal Antosik

                            Hi All

                             

                            this is working, not nice but working.

                             

                            1. create on local machine/server ODBC connection to SQL using native client

                            it will not allow to save password, and the problem is it must be user DSN,  QV does not see system DSN

                             

                            2. go to QV and choose OLE DB -  Microsoft OLE DB Provider fo ODBS Drivers

                            here in data source name put the name you have created in point 1 and use option save pass

                             

                            now is working, the problem is this must be done on account that QV is using if you want to reload automaticaly

                              • Re: OLE DB SQL Native Client connection
                                Dave Riley

                                Rafal,

                                 

                                Be careful doing that. I had issues with incorrect data coming through and bit values may be handled differently.

                                 

                                http://community.qlik.com/thread/46600

                                 

                                Also, the current view is ODBC can perform as well as OLEDB and Microsoft are unclear as to the future of OLEDB. See the support status section of the OLE_DB wikipedia page ...

                                 

                                en. wikipedia. org/wiki/OLE_DB

                                 

                                "Support status

                                Microsoft's release of SQL Server 2012  (internal code: 'Denali') is the last to include an OLE DB provider for SQL Server, but support will continue for 7 years.According to a related Microsoft FAQ, "Providers like ADO.Net which can run on top of OLE DB will not support OLE DB once the latter is deprecated", but the same answer in the FAQ states that the original post relates only to the OLE DB provider for SQL Server, so the position of OLE DB itself remains unclear. The same FAQ states that ODBC performs better than OLE DB in most cases."

                                 

                                flipside