InfoPath Dev

On the database I am trying to connect to, I added the DBXL service account that I used to set up DBXL, to logins on SSMS (overall) Security.  Then I added that same user account to the database I wanted to access and granted  db_datareader adn db_datawriter.  I am able to access the database using Query Builder however when I attempt to create the receive web service data connection I get the following error.  What am I missing?  This is my first time using QueryDB withOUT a tutorial so maybe I missed something?  I originally had my form using DBXL connection to a different server.  Then I moved the database to this new server.   

The SOAP response indicates that an error occurred on the server:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> Qdabra.QueryDB.QueryDbException: Error D0101:
----------------------
The specified database was not found when querying the SQL server.
Either the query contained an invalid database name or the QueryDB configuration is incorrect.
The error returned from the database server was:
Cannot open database "DB1" requested by the login. The login failed.
----------------------

   at Qdabra.QueryDB.QueryDBService.PerformQuery(String connectionString, String selectQueryString, MySqlParameterCollection sqlParams)
   at Qdabra.QueryDB.QueryDBService.GetRequestedData(String database, String table, List`1 columns, Boolean distinct, String whereClause, String orderByClause, Int32 maxRows, Boolean countOnly, MySqlParameterCollection sqlParams)
   at Qdabra.QueryDB.QueryDBService.InternalGetDataSet(XmlNode queryXml, String& table, Boolean& countOnly)
   at Qdabra.QueryDB.QueryDBService.Qdabra.QueryDB.Services.IQueryDBService.GetColumnsXMLQuery(XmlNode queryXml)
   at Qdabra.QueryDB.QueryDBService.Qdabra.QueryDB.Services.IQueryDBService.GetColumnsXMLStrQuery(String queryXml)
   --- End of inner exception stack trace ---

--UPDATE:  I think I was supposed to use DAT tool to set it up first....I skipped that step. Oops.

Hi Doreen,

When Query Builder connects to the database, it is doing so as you (the user).

However, when the web service connects, it does so as the service account.

Please check the application pool for DBXL ; this will tell us what the service account is. Then add SQL permissions for that account.

This should resolve the issue, but let us know if it does not. Thanks.

I'm not sure what this means:

Doreen:

I originally had my form using DBXL connection to a different server. Then I moved the database to this new server.

Was DBXL reinstalled?

No.I didn't reinstall DBXL.  I'm just pointing DBXL to a different SQL server to retrieve data than the first time I used it.  Different form, different server, different database. 

I am now having trouble connecting to SQL through DAT.  I am using the correct app pool to access the database, DB1 in my example...and somehow I managed to change DAT tool to offline mode. How do I make in online?

In this scenario, it's not DAT that goes offline but rather InfoPath itself. I would close all the instances of InfoPath Filler, then reopen Filler and look under File to see if the option to go Online shows up.

For troubleshooting the connectivity issues, what I typically do is look for the QdabraDBXL database that is currently in use by your installation of DBXL. Then look at its permissions and compare those to the databases you are mapping (via DAT) or reading (via QueryDB). This usually helps me identify the missing permission and correct it.

Hope this helps.

OK I went looking at QdabraDBXL database and it's installed on that first server, DB2 (my developement SQL server), not the one I am using now, DB1 (my production SQL server), Is that the problem? 

Also the permissions are the correct app pool used to install DBXL   however I checked  datareader and datawriter in owned schemas and Role members whereas in QdabraDBXL, these permissions are only checked in Role Members.  Ut oh.  Is that a problem?  I can't uncheck Owned schemas....

In theory, you can have DBXL installed on SQL_SERVER_1 and have QueryDB retrieve data from SQL_SERVER_2 and map data to SQL_SERVER_3, as long as all permissions are set correctly. It's just a little more difficult to manage with all the different connections.

In the case where DBXL is being accessed by a remote server, then you'll need to grant permissions to the machine. To continue with the example above, let's say the machine name for the DBXL server is:

DOMAIN\Dbxl-server

When you add permissions in SQL (for mapping or for querying) you'll need to grant the machine permissions. In SQL, the machine names are granted permissions in this format:

DOMAIN\Dbxl-server$

Here's a screenshot of what this looks like on one of our servers.

Thank you for explaining this to me.  I am learning a million different things on this project.

It turns out that after I removed the DBXL app pool user from  datareader and datawriter as owners of the schema (by going to Schemas under Security), then adding app pool to Role Members instead, it all started working! WOO HOO