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.

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
..
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
...
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?
Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT