Question about encrypting a current App.

When we convert an existing app to start encryption and decrypting data as needed, I am required to make changes to atleast 50 stored procs. Within each of these SPs I do:

-- OPEN SYMMETRIC KEY

-- do the ecrypt or decrypt operation

-- CLOSE SYMMETRIC KEY

Is it possible to not use these steps in each of my SP's? i.e, can we generalize the open and close key calls somewhere else? Any tips are greatly appreciated. TIA.

You can open the symmetric keys as part of connecting to the database and close them as you log out. Anything executing in that context will have access to the keys and be able to encrypt and decrypt with them.

If your application opens/uses multiple connections on behalf of a user and is supposed to encrypt/decrypt on all of them, you would need to open the keys when you open each connection and close them when you cease using a connection. If connections are not pooled, you don't need to close the keys if you're going to kill the connection anyway.

Thanks
Laurentiu

question about different query results with wildcard

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results

Could someone explain or point me to more documentation on the difference results we get?
Thanks

To see the 'wildcards' used with T-SQL, look up topic: LIKE in Books Online.

The asterisk is not a T-SQL wildcard. You queries using an asterisk are most likely returning you counts where the last character is an asterisk. AND you MUST use the LIKE equality instead of equals (=).

Here is a small example of using Wildcards in T-SQL:

SET NOCOUNT ON

CREATE TABLE #MyTable
( MyTableID int IDENTITY,
MyString varchar(50)
)

INSERT INTO #MyTable VALUES ( '1234' )
INSERT INTO #MyTable VALUES ( '1234-1' )
INSERT INTO #MyTable VALUES ( '1234*' )
INSERT INTO #MyTable VALUES ( '1234-45' )
INSERT INTO #MyTable VALUES ( '1234-ab' )

SELECT '1234', count(1) FROM #MyTable WHERE MyString = '1234'
SELECT '1234*', count(1) FROM #MyTable WHERE MyString = '1234*'
SELECT 'LIKE ''1234''', count(1) FROM #MyTable WHERE MyString LIKE '1234'
SELECT 'LIKE ''1234%''', count(1) FROM #MyTable WHERE MyString LIKE '1234%'

DROP TABLE #MyTable

|||Hi Arnie, thanks for your response.

My situation is more basic than what appears in your example. Unfortunately, I am working solely through this application so I do not have access to the SQL to test out what you supplied, so my question is more of a need for an explanation of the search results. I do find lots of references to LIKE, Wildcards and pattern matching, but I don't find a way to explain to the users the best and most complete way to search. They mostly need a "this will always get us the results without missing anything" search technique and then to be able to select from a smaller group. I guess I need a basic course in understanding search results: how to get different ones and what they mean.

Using
Note contains 94949-'%'
returned one more result than when using an asterisk. I don't understand this difference.

Note contains 94949-'%1' or
Note contains 94949-'1%'
brings nothing nor does not using quotes. But there are hundreds of records which have the string starting with 94949-1 and a varying number of characters after that.
?Does the dash read not as a character in the string but as an expression?

When I use WITHIN 3 characters, I get too few results (eight). If I use AND, I get text unrelated to the account number I am looking for.

Again when I tried to narrow the search by adding one digit to the string to be matched, I did not get any results, but 500 results from the more general search is too much to scan by opening individual records.

Thanks for pondering this with me.

|||Assuming that the application is using SQL Server Full-Text Indexing, the rules would be defined here
http://msdn2.microsoft.com/en-us/library/ms187787.aspx

Question about authentication

Hello :).

I have a web application running under .NET 1.1 and Reporting Services 2005. What I need is to display reports in my app.

Here I have two problems:

1. SSRS uses web service as a data source (it is in my application). Access to the service should be permitted only for known (and logged in) users.

2. Access to SSRS should be permitted only for known (and logged in) users.

So, what is the best approach to integrate Reporting Services and my application according to the security limitations?

Any advise on this subject is more than welcome :)

Thank you.

You have basically two options - trusted account and custom security. See this article for more info. I am curious to know how SSRS uses a web service as a data source. Did you write a custom data extension?|||

Teo, thank you very much for your great articles and reply. They were very helpful for me.

Talking about web service as a data source I mean simple XML data source. I.e. DataProvider is 'XML', and connection string refers to the web service. So, I didn't write any custom data extension, as it was already written in the SSRS 2005 :).

Sorry if original post was unclear (maybe it's because I'm really novice in the Reporting Services :) ).

Question -- Demo application on a CD

We have an application that accesses data from a SQL Server (2000) database.
The app is written in C# and uses SQL connections, SQL data adapters, etc
. At this time, the app uses approximately 50-60 SQL adapters.
We would like to place a demo version of the program on a CD that could be i
nstalled on a PC that does NOT have SQL Server installed and does not have a
ccess to a SQL Server installation, and without having to completely recode
all data access in the appl
ication.
Looking for any and all suggestions on how to accomplish this?MSDE is the way to go but i need instalation.
Bojidar Alexandrov|||You can distribute MSDE with the application which should be enough for a de
mo.
Do a search - there are plenty of articles about distributing it.
You can install msde, create and populate a database with the installation.

Question -- Demo application on a CD

We have an application that accesses data from a SQL Server (2000) database. The app is written in C# and uses SQL connections, SQL data adapters, etc. At this time, the app uses approximately 50-60 SQL adapters.
We would like to place a demo version of the program on a CD that could be installed on a PC that does NOT have SQL Server installed and does not have access to a SQL Server installation, and without having to completely recode all data access in the appl
ication.
Looking for any and all suggestions on how to accomplish this?
MSDE is the way to go but i need instalation.
Bojidar Alexandrov
|||You can distribute MSDE with the application which should be enough for a demo.
Do a search - there are plenty of articles about distributing it.
You can install msde, create and populate a database with the installation.