locked
Database not accessible by DQS Error RRS feed

  • Question

  • Hi,

    I created a new KB using the existing sample domain that ships with CTP3. Then while doing the Knowledge Discovery, I came to the first step (Map) of Knowledgebase Management wizard.

    I selected SQL Server as datasource, AdventureWorks2008R2 (Denali version for CTP3 available from Codeplex) as database, and Address table as the table.

    When I click on Preview Data Source, a pop up appears and I am able to see all the data from this table.

    After that when I did mapping for a single column, City - City (built in domain), and then I click on Next. On clicking Next, I get the below error.

    I am not able to understand that one the same page when I am able to preview data from the table, when I click on Next button the below error says that DB does not exist at all. Due to this error I am completely stuck to proceed any further. Any help on this is greatly appreciated.

    Error:

    SQL Server Data Quality Services
    --------------------------------------------------------------------------------
    Message Id: DataServiceDatabaseDoesNotExist
    Database 'AdventureWorks2008R2' does not exist in the current SQL Server or is not accessible by DQS.

    --------------------------------------------------------------------------------
    Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: Database 'AdventureWorks2008R2' does not exist in the current SQL Server or is not accessible by DQS.
       at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec()
       at Microsoft.Ssdqs.Proxy.EntryPoint.MetadataManagementEntryPointClient.DataSourceMappingCreate(DataSourceMapping dataSourceMapping)
       at Microsoft.Ssdqs.Studio.ViewModels.Data.DataSources.DataSourceMappingProvider.SaveDataSourceMapping(DataSourceMapping dataSourceMapping)
       at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Common.DataSourceMappingsViewModel.SaveDataSourceMapping()

     

    Regards,

    Siddharth Mehta, SQL Server MVP

    (http://siddhumehta.blogspot.com)


    --Siddharth Mehta http://siddhumehta.blogspot.com
    Monday, July 18, 2011 12:00 PM

Answers

  • Hi,

     

    It seems that this problem is caused by a bug that we have, that is related to handling of special column types such as geography. The Address table includes a column of this type.

    The error message in this case is wrong and the problem is not related to security settings. We plan to fix this bug in the next milestone.

     

    Just to verify that this is indeed the problem - does any of you experience this problem also with other tables, that do not include special types such as geography and image?

     

    Thanks,

    Omer

    • Proposed as answer by Víctor M Friday, July 29, 2011 10:32 AM
    • Marked as answer by Omer Boker [MSFT] Tuesday, August 2, 2011 3:03 PM
    Tuesday, July 26, 2011 9:24 AM

All replies

  • Hi Siddhartha,

    This error occurs because the two DQS logins created during the DQS server installation, ##MS_dqs_db_owner_login## and ##MS_dqs_service_login##, do not have appropriate privileges on the source database. To fix the issue, grant appropriate privileges to the two DQS logins on your source database, and try again. For information about doing so, see Grant Access to Data for the DQS Operations.

    This issue is also documented in the DQS operational troubleshooting article here.

    Thanks,
    Vivek
    Technical Writer, DQS

    Monday, July 18, 2011 7:12 PM
  • This solves the problem for tables having the DBO schema. But still other tables having schema like Person, HumanResources, Sales etc are not accessible.

     

    Regards,

    Siddharth Mehta, SQL Server MVP

    (http://siddhumehta.blogspot.com)


    --Siddharth Mehta http://siddhumehta.blogspot.com
    Tuesday, July 19, 2011 9:43 AM
  • Hi Siddharth,

     

    As Kumar described, this is a limitation in CTP3, that is already fixed in our next release. You need to provide access permissions to the DQS logins (##MS_dqs_db_owner_login## and ##MS_dqs_service_login##) to any table that you try to run a compute process on (discovery, cleansing, matching).

    If you grant access across the db, all tables will be served, but it seems like you granted access to a specific table.

    As for previewing - the preview mechanism in CTP3 uses the user permissions in the user context, hence you do not encounter this problem.

    Again, in the current build we have greatly improved this behavior, by providing both a staging environment for you to load your data into, and by allowing to use the user context and permissions when trying to access data.

     

    Hope this helps,

    Elad

    Tuesday, July 19, 2011 12:49 PM
  • Hi Elad,

    I have given Control level access on the entire DB, even tried giving explicit full permissions on all the schemas. But it reads only tables in DBO schema, and others it is not able to access. I even transferred few tables from other schema to DBO schema, but still those tables are not visible / accessible to DQS. Is there something that I am missing or is there an issue with AdventureWorks DB CTP3 Denali version ?

    It happens only with AdventureWorks DB CTP3 Denali version. I created a new DB, gave CONTROL level access to the DB, and created table in DBO as well as DQS schema, and it works fine with DQS. Not sure what's the gap / issue with ADW DB.

     

    Regards,

    Siddharth Mehta, SQL Server MVP

    (http://siddhumehta.blogspot.com)


    --Siddharth Mehta http://siddhumehta.blogspot.com
    Wednesday, July 20, 2011 9:48 AM
  • Hi Siddharth,

     

    We will investigate this issue and get back to you.

     

    Thanks,

    Omer

    Wednesday, July 20, 2011 2:13 PM
  • I'm experiencing the same issue

    Please report the solution when you find it.

    Thanks in advance

     


    Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
    Saturday, July 23, 2011 2:38 PM
  • Hi,

     

    It seems that this problem is caused by a bug that we have, that is related to handling of special column types such as geography. The Address table includes a column of this type.

    The error message in this case is wrong and the problem is not related to security settings. We plan to fix this bug in the next milestone.

     

    Just to verify that this is indeed the problem - does any of you experience this problem also with other tables, that do not include special types such as geography and image?

     

    Thanks,

    Omer

    • Proposed as answer by Víctor M Friday, July 29, 2011 10:32 AM
    • Marked as answer by Omer Boker [MSFT] Tuesday, August 2, 2011 3:03 PM
    Tuesday, July 26, 2011 9:24 AM
  • Just to verify that this is indeed the problem - does any of you experience this problem also with other tables, that do not include special types such as geography and image?


    Yes, I did the cleansing operation against the table and mapping the field DimCustomer AddressLine1 with Address Line (Address Check) and works correctly.

     Thank you for clarify.

     


    Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
    Friday, July 29, 2011 10:32 AM
  • Not fixed in 2012 RTM!

    Friday, April 6, 2012 10:32 AM
  • Do you have the data types mentioned? geography and image data types in the source table?

    Thx, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Saturday, April 7, 2012 4:04 PM
  • Hi,

    This issue is now documented in the DQS Operational Troubleshooting guide here.

    Thanks,
    Vivek
    SQL Server User Education

    Thursday, April 12, 2012 9:50 AM
  • Vivek,

    I'm evaluating the SQL 2012 RTM, and this problem still exists in the DQS Client. 

    The issue is also apparent in certain tables in the AdventureWorks database that contains none of the problem datatypes.  For example the Employee table which contains BirthDate (date), BusinessEntityID (int), Gender (nchar), HireDate (date), JobTitle (nvarchar), LoginID (nvarchar), MaritalStatus (nchar), NationalIDNumber (nvarchar), OrganizationLevel (smallint), SickLeaveHours (smallint), VacationHours (smallint).

    There are none of the following datatypes as stated in the Troubleshooting Guide:  geography , geometry , image , and hierarchyid

    Can you please confirm the full extent of the issue so we can decide if we will use the DQS Client or another tool?

    Thanks

    Dan

    Wednesday, May 16, 2012 11:13 AM
  • Hi Dan,

    Thanks for your feedback. I will look into this issue and revert soon.

    ~ Vivek

    Wednesday, May 16, 2012 7:39 PM
  • Hi Dan,

    In the Employee table in the AdventureWorks2012 database, I can see that the OrganizationNode column is of hierarchyid data type:

    I deleted the OrganizationNode column (had to first delete the OrganizatioLevel column as it is dependant) from the Employee table, and tried remapping the columns in the DQS client to the columns in the Employee table, and it worked fine for me.

    So, the behavior is the same as documented in the troubleshooting article. Please note that if any column in your source table has one of the four data types that is mentioned in the troubleshooting article, you will face this issue.

    Also, please look at this topic for the list of supported SQL Server and SSIS data types that can be mapped against each of the 4 data types in DQS: Supported SQL Server and SSIS Data Types for DQS Domains.

    Please let me know if it works for you as well. Pls contact me if you face any further issues.

    Thanks,
    Vivek
    SQL Server User Education

    Thursday, May 17, 2012 6:48 AM
  • You can make a view that grabs only the columns that work if needed. This problem is a good DCR for DQS - when you don't map a certain column's data type, don't block the user from picking a table with that column in it because the column goes unused.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    Thursday, May 17, 2012 2:16 PM