Database not accessible by DQS Error

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Hi Siddharth,

 

We will investigate this issue and get back to you.

 

Thanks,

Omer

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

Not fixed in 2012 RTM!

Answer 10

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

Answer 11

Hi,

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

Thanks,
Vivek
SQL Server User Education

Answer 12

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

Answer 13

Hi Dan,

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

~ Vivek

Answer 14

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

Answer 15

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