Does Force Protocol Encryption on Server Require Certificate to be Installed on Client?

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.
If found a good article.
http://msdn.microsoft.com/library/de...SecNetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.google. com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client?

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If found a good article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.google.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If you have a certificate on the Server and Force Protocol Encryption
turned on then the traffic is encrypted to and from all clients. The client
does not need a certificate.
Look at the following article:
HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft Management
Console
http://support.microsoft.com/?id=316898
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Previous post:
"Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
"
The client only requires that the Trusted Root is updated if you enable
protocol encryption from the client.
If it is enabled via Server Network Utility, then the client doesn't need
to Trust the same Root Authority.
Essentially, all you need is a valid certificate on the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||If found a good article.
NetHT19.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
NetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.go
ogle.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Does Force Protocol Encryption on Server Require Certificate to be Installed on Client

So I was able to enable the option to Force Protocol Encryption using
SQL. However, I was still able to connect to this instance of SQL
using the Query Analyzer tool from a machine without the certificate
(public key) installed.
This leads me to believe that the client connected to the server w/out
encrypting communications or that the client doesnt require a
certificate in order to connect (encrypted) to the instance of SQL now
configured to force protocol encryption. As a result, I have the
following question.
Once protocol encryption is enabled on the server using the SQL Server
Network Utility, must the certificate (public key) be installed on the
client in order to access SQL on the server (now configured to force
protocol encryption)?
What does it mean when I connect to the instance of SQL from a machine
w/out the certificate? How is this working? Are communications still
encrypted from client to server?
Thanks in advance for any help/direction.If found a good article.
NetHT19.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
NetHT19.asp
"Note that, in all cases, SQL Server sends its server authentication
certificate in the clear to the client at the beginning of the
communication sequence. This is part of the SSL protocol. Note that
this occurs even when neither the server nor the client requires
encryption."
nasteric@.yahoo.com (nasteric) wrote in message news:<e651d8ae.0406012052.5e1c2f41@.posting.go
ogle.com>...
> So I was able to enable the option to Force Protocol Encryption using
> SQL. However, I was still able to connect to this instance of SQL
> using the Query Analyzer tool from a machine without the certificate
> (public key) installed.
> This leads me to believe that the client connected to the server w/out
> encrypting communications or that the client doesnt require a
> certificate in order to connect (encrypted) to the instance of SQL now
> configured to force protocol encryption. As a result, I have the
> following question.
> Once protocol encryption is enabled on the server using the SQL Server
> Network Utility, must the certificate (public key) be installed on the
> client in order to access SQL on the server (now configured to force
> protocol encryption)?
> What does it mean when I connect to the instance of SQL from a machine
> w/out the certificate? How is this working? Are communications still
> encrypted from client to server?
> Thanks in advance for any help/direction.

Do we really need query based optimisation?

Hello,

I was playing with the Query based optimisation in SQL2005.

- it is not available by default, you have to enable the query log in the server properties

- it generates 1 out of 10 queries log entry

- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

- tried to optimise the partitions based on this mechanism and did not see any performance boost.

I wonder in which cases this option really makes a difference.

I went through all the dimensions design best practices I could find and I finally came to the conclusion that my front-end tool of predilection (Excel 2003) is the problem.

Not sure if anyone did a test on speed boost of aggregations optimization when using the new Excel 2007 as a front end but even without such optimization, Excel 2007 executes in miliseconds what takes ages in Excel 2003 no matter what optimization.

What is the real advantage of using query based optimisations in ssas 2005?

Since this option is disabled by default, I doubt that it would go any farther than an academic type of optimisation. With the right client, 20 ms un-optimized vs 12 ms optimized would certainly not make a difference in the eyes of an end user.

Also, you tend to loose the optimizations each time you change your cube structure and then have to wait a few weeks untill you have a sample of queries large enough.

Any thoughts?

Philippe

I agree with your doubts regarding query based optimization. In AS2000 I have seen that it delivers improvements. Some MS people in this newsgroup have hinted that we will see improvements in SP2 for SSAS2005.

Regards

Thomas Ivarsson

|||

Dont agree with you guys a bit. Usage based optimization is very useful. Especially in AS 2005.

First to answer some questions:

>>- it is not available by default, you have to enable the query log in the server properties

Yes it is not avaliable by default. Here is whitepaper explaning query log setup and options controlling it: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

>>- it generates 1 out of 10 queries log entry

Take a look at the whitepaper and you'll see the QueryLogSampling property that defiles the frequency of sampling

>>- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

The way you should think of Usage Based Optimization is; You should let it run for awhile and then used it to design aggregatins. After that if you satisfied with UBO, you can stop it.

>>- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

Not sure what you refer to here

>>- tried to optimise the partitions based on this mechanism and did not see any performance boost.

This could be the indication that you dont really need usage based optimization. If your query perofrmance problem could be solved by moving to use another tool, this is good indication, you probably dont need it.

With lots of attributes and poorly designed attribute relationships your aggregations are going to be of little help. Put decent size of data into your cube and see perofrmance going down. Collecting stats and than later designig aggregations for these queries is probably the only way to go in this situation.

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

|||Thank you Edward for the clarification.

By Usage audit I meant I did set the log sampling to 1 out of 1 so I can query the OlapQueryLog table to report on cube usage.

That is a management requirement to see who uses which cube and how frequently.

The log mechanism is not designed to do this however it is the only way I have found to provide this usage information.

If there is a better way, I would be glad to use it. All I need is UserID, database name, Cube name, Date. If I could have the query itself also, that could be a nice thing. I do not need the subcube code.

These days of SOX audits makes it very interesting to be able to tell who is actually using your systems beyond simply providing a list of authorized users.

Regards,
Philippe|||

For audit purposes you can create a server-side trace. Try to see how in SQL Profiler you can create a trace that writes to a file. This way you should get UserID and all other properties logged.

You only need few events selected for this trace for instance: Query Begin, Command Begin, Audit Login, Audit Logout

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