Greetings from an SQL Server newbie.
We are purchasing 3rd party software with a SQL Server back end. A tech person installing SQL Server on the server insisted we use mixed mode authentication. (We are running Windows 2003 using Active Directory) We want Windows authentication (WA) but he claims it is "more complicated" to use.
His example: A shop using WA found that users running a certain process that creates and deletes temp tables did not have permission to delete tables and the process failed. The vendor had to go in and manually delete the tables.
The application uses .INI files with passwords and user names in plain text to connect to SQL Server. Our security specialist's eyes bugged out. :eek: We all think this is not good.
Is the vendor blowing us smoke? Should we insist on WA?
Thanks for your help.Depending on the package, what you want your users to do, network topography, and other issues they might have a point.
Just a few weeks ago some of the folks at a major manufacturer in Moline were having a fit about a package that had been developed internally (by a plant manager) that used SQL authentication. The central admin folks were having a fit, until they discovered that the reason the app was written that way was due to limitations in their internal network that made AD impractical. Sometimes even when you have the source code, other conditions prevent you from being able to make the changes that you'd like!
Even Microsoft came to the conclusion that SQL Authentication was the best option for those conditions, as much as it irked them to have to say that!
-PatP|||The vendor's example smacks of bogosity. Windows authenticated users, and SQL authenticated users can both be granted and denied the same set of permissions. Once a login is authenticated, there is no difference between them. The major reasons we would go with SQL authentication is for webservers. Generally, webservers run several applications (web sites), and each may need their own set of permissions on a set of databases (usually one DB per application). Windows authentication allows for only one login for the server, while SQL authentication allows each application to have its unique login.|||Exactly the problem that they were having. Pseudo-public machines force all users to share a common NT login. If you want users to have different levels of permission, you have to use SQL authentication.
The same problem applies on a more general scale to a WAN that doesn't support NetBIOS completely. Since the workstations can't authenticate over the WAN, the credentials have to be forged... Because they can't authenticate, they all get the same credentials. Same problem as before, but LOTS harder to fix!
-PatP|||So I guess sometimes it makes sense to use SQL Authentication instead of Windows Authentication. But it makes no sense to store username/passwords in an .ini file as plaintext. Perhaps it says someting about the effort put in by the vendor.|||So I guess sometimes it makes sense to use SQL Authentication instead of Windows Authentication. But it makes no sense to store username/passwords in an .ini file as plaintext. Perhaps it says someting about the effort put in by the vendor.I see that as pure idiocy, not really a database issue at all. I'm flat out allergic to storing passwords in plain text anywhere, and generally uncomfortable with storing them on PCs at all (with the exception of tools like SplashId or Password Safe that are designed for just that purpose).
-PatP|||hmmz.. pwsafe comes in handy and secure indeed. btw: it comes with an export-function to plaintext ;)|||hmmz.. pwsafe comes in handy and secure indeed. btw: it comes with an export-function to plaintext ;)I suppose that all tools have to have some flaw! ;)
-PatP
Showing posts with label newbie. Show all posts
Showing posts with label newbie. Show all posts
Sunday, March 25, 2012
Friday, February 24, 2012
attribute key cannot be found - regular hierarchy
I am a newbie in Analysis Services. I have just designed a cube, drawing data from a bigger database. In the cube measures are about responses to questionnairies. There are 3 dimensions: question, time of response and user (who responded).
Sorry for the short delay, my internet connection was just lame this morning.
The full list of errors is the following (I now check it again and I put to bold something I find interesting):
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors and Warnings from Response
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}.
Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.
Finally, I am not even using the greek description in the cube.
The user hierarchy is a user-enterprise-activity hierarchy.
When the cube is processed I receive the following error when it reaches to process the enterprise part:
Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. 0 0
(then it believes to be unknown, it finds too many errors and stops).
This value is a correct value for an activity Id. But I suppose this means that the link between enterprise and activity doesn't work (it's perfectly ok in the relational database). But I cannot find why.
SELECT
DISTINCT
[dbo_Activity].[Id] AS [dbo_ActivityId0_0],[dbo_Activity].[EnglishDescription] AS [dbo_ActivityEnglishDescription0_1],[dbo_Activity].[FrenchDescription] AS [dbo_ActivityFrenchDescription0_2]
FROM
(
SELECT Activity.Id, LocalizedActivity.Description AS EnglishDescription, LocalizedActivity_1.Description AS FrenchDescription,
LocalizedActivity_2.Description AS GreekDescription, LocalizedActivity_2.NationalCode AS GreekCode, LocalizedActivity.NationalCode AS EnglishCode,
LocalizedActivity_1.NationalCode AS FrenchCode
FROM Activity INNER JOIN
LocalizedActivity ON Activity.Id = LocalizedActivity.ActivityId INNER JOIN
LocalizedActivity AS LocalizedActivity_1 ON Activity.Id = LocalizedActivity_1.ActivityId INNER JOIN
LocalizedActivity AS LocalizedActivity_2 ON Activity.Id = LocalizedActivity_2.ActivityId
WHERE (LocalizedActivity.Language = 'en-GB') AND (LocalizedActivity_1.Language = 'fr-FR') AND (LocalizedActivity_2.Language = 'gr-GR')
)
AS [dbo_Activity]
Processing Dimension Attribute 'Enterprise' failed. 1 rows have been read.
Start time: 24/5/2006 5:19:28 μμ; End time: 24/5/2006 5:19:28 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Enterprise].[Id] AS [dbo_EnterpriseId0_0],[dbo_Enterprise].[Name] AS [dbo_EnterpriseName0_1],[dbo_Enterprise].[NationalCode] AS [dbo_EnterpriseNationalCode0_2],[dbo_Enterprise].[PrimaryActivity] AS [dbo_EnterprisePrimaryActivity0_3]
FROM
(
SELECT Id, Name, PrimaryActivity, NationalCode
FROM Enterprise
)
AS [dbo_Enterprise]
Can you post some more information about you situation.
Is error you receive is happening during processing of dimension or partition? If you are processing entire cube, please try to process your dimensions firts and then process partition, by partition.
What is the stucture of your user dimension?
I also see some non-english descriptions in the query, where do they come from?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Sorry for the short delay, my internet connection was just lame this morning.
The problem is happening during cube processing, but also during user dimension processing.
The user dimension comes from this named query (yes it's a bit familiar for those accustomed to asp.net ;)):
SELECT aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.Enterprise, aspnet_Membership.Email
FROM aspnet_Users INNER JOIN
aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
Afterwards in the User dimension hierarcy,
User (...) has Enterprise and User Name as attributes,
Enterprise (..) has Activity, Name_ and National Code as attributes and
Activity (.) has English Description and French Description as attributes
Detailed code in the end of the post
The descriptions, as it is shown in the code in the first post come from LocalizedActivity table. Activity is thus a named query, drawing data from the activity table and the localized activity one (multiple lines for the latter, two for english language, two for french and two for greek - yes I know, it's a strange combination of languages ;)).
[Code for the dimension in case it helps]
<Source xsi:type="DataSourceViewBinding" dwd:design-time-name="fae66f4d-967b-400e-9dd5-fe6ba89a6771">
<DataSourceViewID>Local Sql Server</DataSourceViewID>
</Source>
<UnknownMember>Visible</UnknownMember>
<CurrentStorageMode>Molap</CurrentStorageMode>
<Attributes>
<Attribute dwd:design-time-name="623f18b9-82f5-4641-9b53-e159ef88c0a3">
<ID>Aspnet Users</ID>
<Name>Aspnet Users</Name>
<Usage>Key</Usage>
<EstimatedCount>2</EstimatedCount>
<KeyColumns>
<KeyColumn dwd:design-time-name="7c5aecb6-c79f-489f-b16d-517a00536a99">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="5c6894e9-7c7e-4d9e-a5e0-0db5e73af31a">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserId</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="9a225117-5e19-4e93-98ca-1f9c8aecbb34">
<AttributeID>User Name</AttributeID>
<Name>User Name</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="4bd453e1-04c5-44d4-925a-07b781710eb9">
<AttributeID>Enterprise</AttributeID>
<Name>Enterprise</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="4d504c73-5be5-4643-ad53-5945dbe0093e">
<ID>User Name</ID>
<Name>User Name</Name>
<Type>PersonFullName</Type>
<EstimatedCount>2</EstimatedCount>
<KeyColumns>
<KeyColumn dwd:design-time-name="1e48b643-917b-4cab-ac8e-c2b5b5836ef3">
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="6047a2f6-953b-428a-86bf-59a4a716c9bb">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserName</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn dwd:design-time-name="c8a05998-f1cb-4a4b-bfbc-dc6412078020">
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="72d2f7aa-9128-4f32-8c99-3393eade848f">
<TableID>dbo_aspnet_Users</TableID>
<ColumnID>UserName</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="5eda6b5e-78ef-4c22-8000-3a93dd23d6a4">
<ID>Enterprise</ID>
<Name>Enterprise</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="9c5e9ce9-d304-4fb3-b1c4-cbffee628798">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="b1fea07e-c3cd-4811-aebc-21d90d834bec">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Id</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn dwd:design-time-name="b50a3458-488b-45a2-8e33-5237b26fe3b6">
<DataType>WChar</DataType>
<DataSize>50</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="13b0aaa9-c817-46b5-9609-36039dc8e487">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="4a5648da-1625-4b0d-9911-41c45ab64325">
<AttributeID>National Code</AttributeID>
<Name>National Code</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="1f5d1cdb-3872-4d6b-97fc-e81cabd89429">
<AttributeID>Name</AttributeID>
<Name>Name_</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="c7be6cbf-f255-420c-8d70-c4b24d80706f">
<AttributeID>Activity</AttributeID>
<Name>Activity</Name>
<Visible>false</Visible>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute dwd:design-time-name="5fa36ff1-0d08-4336-9bd7-90c15a6baf53">
<ID>National Code</ID>
<Name>National Code</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="8ec125a2-426e-41d1-bdf2-5c07b540b41b">
<DataType>WChar</DataType>
<DataSize>16</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="eaeb27bc-d602-4e02-992c-8d619633b365">
<TableID>dbo_Enterprise</TableID>
<ColumnID>NationalCode</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="5248c844-b5b4-4946-be3b-cd035c7d36d7">
<ID>Name</ID>
<Name>Name</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="30bbddd9-ebb1-4570-ac88-1825911cca8d">
<DataType>WChar</DataType>
<DataSize>50</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="7b76b669-47bf-403e-b5e9-c95480031621">
<TableID>dbo_Enterprise</TableID>
<ColumnID>Name</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="94b00c3c-bdb6-434e-93be-59cd8b702c86">
<ID>Activity</ID>
<Name>Activity</Name>
<KeyColumns>
<KeyColumn dwd:design-time-name="7e570409-f592-460b-83bb-7d36bd5c83ef">
<NullProcessing>UnknownMember</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding" dwd:design-time-name="633bf76c-9410-4513-9933-b3439975ce44">
<TableID>dbo_Activity</TableID>
<ColumnID>Id</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<AttributeRelationships>
<AttributeRelationship dwd:design-time-name="376c55ce-bff9-48a1-bc19-0985819f9af8">
<AttributeID>Description</AttributeID>
<Name>EnglishDescription</Name>
</AttributeRelationship>
<AttributeRelationship dwd:design-time-name="612f91ad-e799-4aaa-bf7f-08a13a7a69ec">
<AttributeID>EnglishDescription 1</AttributeID>
<Name>FrenchDescription</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="e6bcad35-b68c-477a-a03a-3f6b5de37d69">
<ID>Description</ID>
<Name>EnglishDescription</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="6a48f875-38dd-449f-8c4b-a037be5851b4">
<DataType>WChar</DataType>
<DataSize>128</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="aa6ce269-8d62-408c-b7a9-538eee58708c">
<TableID>dbo_Activity</TableID>
<ColumnID>EnglishDescription</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute dwd:design-time-name="51245cda-daf1-43d5-9c16-83f03c58c84d">
<ID>EnglishDescription 1</ID>
<Name>FrenchDescription</Name>
<Type>Caption</Type>
<KeyColumns>
<KeyColumn dwd:design-time-name="16b37f49-cbd4-4e2b-ad2f-87053b15083b">
<DataType>WChar</DataType>
<DataSize>128</DataSize>
<Source xsi:type="ColumnBinding" dwd:design-time-name="4fcc5408-b96d-4e24-b3d6-43bb33ca345b">
<TableID>dbo_Activity</TableID>
<ColumnID>FrenchDescription</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<OrderBy>Key</OrderBy>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy dwd:design-time-name="80e30aa7-1d98-49a3-ae89-14022ba5ff24">
<ID>Activity - Enterprise</ID>
<Name>Activity - Enterprise</Name>
<Levels>
<Level dwd:design-time-name="aaba10df-78c3-4509-b520-52b6e6fe656f">
<ID>Activity</ID>
<Name>Activity</Name>
<SourceAttributeID>Activity</SourceAttributeID>
</Level>
<Level dwd:design-time-name="05e46e50-bb6e-4f14-b4c8-2609dc5a9fb6">
<ID>Enterprise</ID>
<Name>Enterprise</Name>
<SourceAttributeID>Enterprise</SourceAttributeID>
</Level>
<Level dwd:design-time-name="348e276e-dc64-4665-b8ac-64f5739b40c4">
<ID>Aspnet Users</ID>
<Name>User</Name>
<SourceAttributeID>Aspnet Users</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>|||
It is bit strange...
Your error indicates:
Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}
But little below from there I see error message: Processing Dimension Attribute 'Enterprise' failed. 'Enterprise' attribute is based on the dbo_Enterprise table.
Processing of which attribute is failing?
Another observation, are you running Enterprise version of Analysis Services? In this case you should be able to take advantage of Translations feature and get rid of extra description in French and Greek.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
The full list of errors is the following (I now check it again and I put to bold something I find interesting):
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors and Warnings from Response
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Enterprise' attribute of the 'User' dimension from the 'QueStorm' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Activity, Column: Id, Value: {F1B5099A-F1D4-4156-945E-0D733EB71C8C}.
Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Enterprise of Dimension: User from Database: QueStorm, Record: 2.
I run Standard version. In any case, I need the French descriptions for the relational part of the application (so the table is already there, filled).|||
The problem I beleive is following:
When processing your Activity attribute, Analysis Server will send a SQL query to read all ActivityId's and will save these ActivityId's as keys for Activity attribute
While processing Enterprise attribute, it will send SQL query for EnterpriseId and also for matching ActvityId to know what is going to be the parent Activity for specific Enterprise.
Try copy SQL query for Activity attribute from processing dialog and append "Where" clause to it with ActivityId you getting from the Enterprise attribute error and send it directly. I suspect you will get an empty result.
The problem could be with the way you defined your Descritpion columns in SQL server. You need to make sure you set the collation correctly on Descriotion columns.
But this just a theory. Try doing the experiment above and see if get any results from the SQL query.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Send it directly to where? Try it in Server Management Studio, that is? Or modify the named query for the Enterprise?
Thank you very much, I suspect some recent changes I made to the enterprise description (I tried to use the enterprise name as the description field of the enterprise instead of the key and I suspect it didn't work)
|||When running the query with the above id in the data view query editor, the results are ok (not empty).
Changing the name attribute didn't solve the problem
I send here an image in case it helps:
http://recursive-cacophony.net/tec-goblin/Irrelevant/UserDim.PNG
BTW, in ff this control works weirdly (I cannot use html code)|||
May be the last thing before I give up:)
Try creating new tables for your French and Greek descriptions: LocalizedActivity_1_F and LocalizedActivity_2_G
When creating these tables set collation for Description column to Latin1_General_BIN (I assume you are using SQL Server as your relational database ).
Populate these tables with data from LocalizedActivity_1 and LocalizedActivity_2, change your named query to point to the new tables and try processing dimension again.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Finally, I am not even using the greek description in the cube.
In SQL Server Management Studio I can see data in all languages perfectly, and all fields are nvarchar, in the Cube they are wchar, I don't see many ways this could be a problem... :S|||Well, I finally found what was the problem. It had not to do with the cube, nor with the collation. It is actually in the select statement I have in the first post:
Using INNER JOIN (as the query designer always does) is a bad idea when sometimes the entries do not exist : for example there are no greek nor english descriptions for the activities right now in the database (it is for future use). So, if you use inner joins instead of nested select statements, you lose all the row, so instead of something like:
[anactivityId] [anactivityFrenchDescription] null null
you have no row at all.
Subscribe to:
Posts (Atom)