Force Encryption Login not working

Hi,
i've enabled the certification authentication login i use SQL 2K5 ENT
my problem is like that: in the client network utility i check the Force protocol encryption and connect
to the server the client can't connect because he doesn't have the certificate install on his machine which it's OK.
the problem is when i remove the Force protocol encryption on the client he connect successfully without the
certificate install on his machine or any thing.
i double check that on the server i've YES on the Force Encryption in the protocols For MSSQLSERVER.
why is that?

THX

Hi Avi,

To clarify, SQL Server does not support any certificate authentication -- certificates are used to facilitate SSL encryption only. When encryption is forced on the client-side, the client will perform server validation to ensure that the certificate came from the correct host. In order for this validation to succeed, the client must trust the root signing authority of the certificate. If the server, and not the client, enforces encryption, then the client will not perform server validation. Furthermore, if a certificate hasn't been provisioned on the server machine, the server will use its self-generated cert for channel encryption. Obviously, the self-generated cert would fail server validation (see this post for more details: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx) so this is something to watch out for when forcing client-side encryption. I believe that this explains the behaviour that you are seeing.

Il-Sung.

Force Encryption

I want to configure SQL 2k5 Express so that all connections are encrypted. I've found the documentation that gives the How-To steps, but I don't see the SSL option in the Network Configuration.
Is this possible with the Express edition?
Do I need to do something special with the installation?
Thanks,
OldSam

Yes. It's possible.

Use SQL Server Configuration Manager, right click on "Protocols for MSSQLSERVER" (or other instance) on the left window, chose "Properties". You will see "force encryption" option. This is for server, which will make sure channel is encrypted.

For client, you right click "SQL Native Client Configuration", you can force encryption as well. Client force encryption means that the client will verify the server and make sure the server is the correct one that it tries to connect, it's not actually about channel encryption.

Force at least one parameter (2K5)

I am trying to build a report that has about 6 different parameters. All of the parameters have been set to allow null, however, I would like the user to put in at least one "search" criteria in order to run the report.

How would I be able to do that?

In addition, when I am viewing the report, it automatically runs and does not wait until I click "View Report". How can I set it not to execute when the report is viewed, but when the user clicks the button?

ThanksHow about setting one of the parameters to not allow blank but setting the default to blank? Might solve both problems in one go, at least forcing the user to think about what they enter... Haven't tested it though |||

The program will not allow you to set a blank default value with the "Allow Blank Value" checked.

Thanks for trying ;)

|||

DotNetSavvy,

Unfortunate if all of your parameter have null values and nulls are allow, then your report will automatically run. I personally set default to all my parameters but one.

On the forcing a parameter selection, you could raise an return error from SQL if all parameters are null.

hammer

|||

Solution:

Create embedded code function that takes as parameters your report parameters values (like this MyFunction(p1value, p2value, p3value,...) ) Inside do something like this: iff(IsNothing(p1value)=true,0,1)+iff(IsNothing(p2value)=true,0,1)+... and if the result is < 1 then throw error/message box with some text (like: "Please assign at least one parameter value") and return null else return 1 or other not null value.

Add internal parameter that cannot be null to your report and in the "expression" of the default value use your code function: =Code.MyFunction(p1.value, p2.value, ...).

Here is an example of using function and parameter to validate report execution http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=829175&SiteID=1

Should work.

Maciej

For XML Explicit in a stored proc

I searched and searched and didn't see what (i think) i need for a
solution.
Here's my situation. SQL 2k5 Stored Proc with
set @.XMLFile = (
SELECT
1 as Tag,
NULL as Parent,
cr.ID as [Resume!1!ID],
cr.GUID as [Resume!1!GUID],
cr.CandidateResumeTypeID as [Resume!1!typeID],
cr.ResumeName as [Resume!1!Name],
NULL as [resume_content!2!!ELEMENT],
lots and lots more.
ending in FOR XML EXPLICIT)
I get an error with this approach. The Query alone outside a proc and
outside the set blah blah = () returns an XML file. I need a proc to do
the same thing.
Thanks!Hello john.wilker@.gmail.com,

> ending in FOR XML EXPLICIT)
> I get an error with this approach. The Query alone outside a proc and
> outside the set blah blah = () returns an XML file. I need a proc to
> do the same thing.
Its always helpful to know the exact error, etc. but, have you considered
using FOR XML PATH instead? I've successfully used that many times in a stor
ed
procedure exactly this way.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

FOR XML AUTO SQL 2K vs 2K5

The upgrade adviser for for 2k5 says something about derived tables being handled differently between 2k and 2K5 and it says to query the tables directly but this does not seem to make much sense because I thought FOR XML AUTO just created some generic XML for presentation purposes. These 2 stored procedures that it is complaining about do query the tables directly and they use the FOR XML AUTO to control the output.

Does anyone know if I have to worry about this? I am tempted to let this slide and check out this part of the application after the migration happens tomorrow for QA to start testing.

Yes I have been googling, checking my books and digging around in BOL. I am not seeing anything.

DISREGARD: I found my derived table. It appears to change the output of the XML. Perfect.I have never used FOR XML AUTO before. Looking over the descriptions of both the SQL 2K and 2K5 versions, the articles read almost identically. If the queries are relatively simple (not based on views, or subqueries), then I think you can just compare the 2K output to a similar 2K5 dataset, to make sure the XML is formed the same way.|||the difference is explained in the obvious location of sp_dbcmptlevel article in BOL 2k5. Off to the doctors office I go. Good thing I have a laptop so I can code in the waiting room.