Encryption with SSAS 2005

Is there a way to encrypt data in SSAS in a similar fashion to the built in encryption provided for a relational database in SQL Server 2005 (SMK to encrypt the DMK, which can then be used to create sym/asym keys to encrypt specific columns)?

If so, how do I create a DSV in SSAS that is based on a table(s) which has encrypted data? Also, how do I encrypt specific fact tables in my cube in SSAS?

Analysis Services does not support encryption of it's data.

As for creating cubes based on the encrypted data within SQL Server, that should not be different from any other application reading encypted data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Encrypting non text data

I want to encrypt some audio files before inserting them in a SQL server database. I want to use the built in sql server encryption. I found that the encrypt by key function accepts only text (nchar,nvarchar,etc).
I was thinking or reading the audio file's bytes and change them to a string and then insert them in the database. I just wanted to take opinions here. Is there a better way to do this?
thanks in advance

Actually, the encryption functions are really designed for binary data, but we accept text variables directly for usability reasons.

Unfortunately you will hit a different problem because of a design limitation on our current implementation: the output cannot be greater than 8k, therefore the plaintext has to be < 8K.

I wrote an article describing the data length limitations and a TSQL-based workaround (http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx), but the workaround proposed in this article has some limitations itself. I would also suggest considering using a CLR module to encrypt/decrypt large BLOBs.

I hope this information will be useful. We appreciate your feedback on this area.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Encrypted password

when we built the login control by built in login control of vs2005 the passwords are saved in the database which is automatically created.how can i get the unencrypted form of these passwords from database.

This post talks about it in great details.

http://mishler.net/2006/04/18/AspNet+Membership+Password+Administration.aspx

hope it helps

|||actually i am a begginer and this articles didnt helped me tell me something else

Empty table still holding Pages

If anyone has seen this before I would appreciate any input.
In a new datamart that was just built there is a worktable for importing new
data. I use DTS to insert the data and then use a delete from statement to
remove the records from the table. Recently the database ran out of space
prematurely and after some investigating I found that with no rows in table
it was still holding space in that datafile. The output from sp_spaceused
worktable is below:
worktable, 0, 571336 KB, 552000 KB, 8 KB, 19328 KB
Also when I run a select * from worktable the query searches the held pages
attempting to return data, even though there are no rows in the table. Has
anyone seen this behavior before? Btw we are using SQL Server 2000 SP3.
--
Jason Strate
j.strate@.comcast.netWhen you fire delete from... statement on the table extent allocation
remains as it is , meaning though the rows gets deleted from the table
allocated extent remains as it is and hence data pages. To remove allocated
extents as well as data you will have run TRUNCATE TABLE command which is
quite faster and does minimally logged operation compare to DELETE FROM
command.
Also to get correct result from sp_spaceused command run DBCC UPDATEUSAGE
... WITH COUNT_ROWS
after any bulk operation like ins/upd/del performed.
To get correct count of rows from the table run query "select count(*) from
table"
--
-Vishal|||Truncate table permissions only default to table owner, members of sysadmin,
db_owner, ddl_admin and is not transferred.
if you are not table owner then you need to be added to ddl_admin/db_owner
database role.
Whereas to run dbcc updateusage you will have to be member of db_owner
databse role
--
-Vishal|||Hello
> In a new datamart that was just built there is a worktable for importing
new
> data. I use DTS to insert the data and then use a delete from statement
to
> remove the records from the table. Recently the database ran out of space
> prematurely and after some investigating I found that with no rows in
table
> it was still holding space in that datafile.
To free space from deleted rows in table you must rebuild or defragment
indexes. To do this you can run:
DBCC INDEXDEFRAG
DBCC REINDEX
DBCC DBREINDEX
CREATE INDEX WITH DROP_EXISTING
You must choose appropriate method according to your conditions.
Serge Shakhov

Empty RS Home-folder after changing IIS properties

Hi,
I've installed Reporting Services on my laptop and built some folders and
reports, works great. But when other persons tried to access my reports they
only got an empty Home-folder when connecting (to http://myserver/Reports). I
tried to change properties at IIS/default website/reports/properties
(directory security/anonymous access and auth control/anonymous access) and
after that also I get an empty Home-folder. I can do/see nothing! As you
already noticed I'm a total newbie to webservers/security/domains etc. Any
tips to get the access to my reports back ? I've changed nothing but IIS
props.
/jerome kThis is not an IIS issue. You have to go in and set security on your folders in
Report Manager. Users must have at least Browser permissions to see anything in
the folders, including your root folder.
/jerome k wrote:
> Hi,
> I've installed Reporting Services on my laptop and built some folders and
> reports, works great. But when other persons tried to access my reports they
> only got an empty Home-folder when connecting (to http://myserver/Reports). I
> tried to change properties at IIS/default website/reports/properties
> (directory security/anonymous access and auth control/anonymous access) and
> after that also I get an empty Home-folder. I can do/see nothing! As you
> already noticed I'm a total newbie to webservers/security/domains etc. Any
> tips to get the access to my reports back ? I've changed nothing but IIS
> props.
> /jerome k|||The problem here is when you set anonymous then everybody is treated the
same, i.e. not given access. Change IIS back the way it was. Then create a
local group and put the people you want to have access to it in the group.
Next read up about roles. RS is role based. You have to put Window users or
groups into a role (using Report Manager). The window user or group can
either be local or domain. I create a local group and then add my domain
user and groups to the local group.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:1C173666-89C0-46EC-86E2-360F3202095D@.microsoft.com...
> Hi,
> I've installed Reporting Services on my laptop and built some folders and
> reports, works great. But when other persons tried to access my reports
> they
> only got an empty Home-folder when connecting (to
> http://myserver/Reports). I
> tried to change properties at IIS/default website/reports/properties
> (directory security/anonymous access and auth control/anonymous access)
> and
> after that also I get an empty Home-folder. I can do/see nothing! As you
> already noticed I'm a total newbie to webservers/security/domains etc. Any
> tips to get the access to my reports back ? I've changed nothing but IIS
> props.
> /jerome k

Empty Recordset

New to Reporting Services.
I have written a stored procedure which I call from a report built with RS.
Certain parameters being passed to the procedure does not materialize a
record set, therefore, the report comes up blank, with no labels etc. What I
need to do is detect an empty record set, from within the report, and set
default values so the report does not appear blank. I have tried the
IsNothing function within each text boxes expressions, however, it does not
work. Has anyone run into this and if so, would you be so kind to provide a
solution?
Regards,
Fred
Regards,
FredHi,
maybe you can use the NoRows property of your table to display the desired
information?
"FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> New to Reporting Services.
> I have written a stored procedure which I call from a report built with
> RS.
> Certain parameters being passed to the procedure does not materialize a
> record set, therefore, the report comes up blank, with no labels etc. What
> I
> need to do is detect an empty record set, from within the report, and set
> default values so the report does not appear blank. I have tried the
> IsNothing function within each text boxes expressions, however, it does
> not
> work. Has anyone run into this and if so, would you be so kind to provide
> a
> solution?
> Regards,
> Fred
>
> Regards,
> Fred|||Hello Ralph,
Thank you for responding. I am not familiar with the NoRows property, do you
have an example? I tried searching the RS BOL but came up empty.
"Ralph Watermann" wrote:
> Hi,
> maybe you can use the NoRows property of your table to display the desired
> information?
> "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > New to Reporting Services.
> > I have written a stored procedure which I call from a report built with
> > RS.
> > Certain parameters being passed to the procedure does not materialize a
> > record set, therefore, the report comes up blank, with no labels etc. What
> > I
> > need to do is detect an empty record set, from within the report, and set
> > default values so the report does not appear blank. I have tried the
> > IsNothing function within each text boxes expressions, however, it does
> > not
> > work. Has anyone run into this and if so, would you be so kind to provide
> > a
> > solution?
> >
> > Regards,
> > Fred
> >
> >
> > Regards,
> > Fred
>
>|||Hi Fred,
You could modify your stored procedure to test whether or not you
retrieve records depending on the parameters you pass. If the query in your
stored procedure does not return records, then you can pass a single record
with the default values in a Select statement, that you want to show in the
report...
Cheers,
Daniel.
"FredG" wrote:
> Hello Ralph,
> Thank you for responding. I am not familiar with the NoRows property, do you
> have an example? I tried searching the RS BOL but came up empty.
> "Ralph Watermann" wrote:
> > Hi,
> >
> > maybe you can use the NoRows property of your table to display the desired
> > information?
> >
> > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > New to Reporting Services.
> > > I have written a stored procedure which I call from a report built with
> > > RS.
> > > Certain parameters being passed to the procedure does not materialize a
> > > record set, therefore, the report comes up blank, with no labels etc. What
> > > I
> > > need to do is detect an empty record set, from within the report, and set
> > > default values so the report does not appear blank. I have tried the
> > > IsNothing function within each text boxes expressions, however, it does
> > > not
> > > work. Has anyone run into this and if so, would you be so kind to provide
> > > a
> > > solution?
> > >
> > > Regards,
> > > Fred
> > >
> > >
> > > Regards,
> > > Fred
> >
> >
> >|||"NoRows" is an element that is a part of the "Properties" for a table. You
can specify a value in the table's "Properties" (under "Data") for the case
when there is no row to display.
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >|||Hello Daniel,
That is exactly what I did to solve the problem. Thanks
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >

Empty Measures

Hi,

I have a problem that confused me and I hope anybody can help me.

I have create a Data Warehouse on SQL Server 2005. Then I built a cube with Analysis Services and deployed them. There are no errors during the processing.
But when I start to browse, there are no data in my Measures. My dimensions are correct an have all data from the data warehouse but every measure are empty.
Maybe, is the number of dimensions restricted? I have 88 dimensions and 1 fact table.

I hope anybody understand my description and can help me.

The number of dimensions is not restricted, although 88 is quite a lot simply from the logical model perspective. You might want to look and see if some of these dimensions should really be combined into fewer dimensions. In Analysis Services 2005, it is best to model one dimension per logical business entity and simply include as many attributes and hierarchies as you are interested in using for that logical entity in that dimension. However, the number of dimensions is not the source of you problem.

Some things to check:

When you process the cube, does it report reading fact table rows?|||Hi Matt,

thanks a lot for your answer.

I have check everything you advise me but nothing helped. So I started to delete all relations in my Data Warehouse. Then I started with two dimensions and tested it. Then was everything o.k.
So I started to add each several dimension. At the end I found my mistake. It was a error based on a primary key which consist of two attributes.|||Thank you so much Matt for answering this question. I lost about a day of work trying to figure it out, but then I stumbled upon your answer. I had accidentally commented out the "CALCULATE;" in my calculated measures. Uncommenting it brought back all my measures.

Jason
http://www.stockpickbloggers.com