I'm here at SQLPass conference in Texas, the preconference doesn't look like it will be "rained out" as my was last year. I came in yesterday in the early evening. There was a little wind on the flight in but nothing major; the plane even arrived on schedule. I look forward to seeing everyone there. If you won't be at the preconference, I'll be doing a book signing on Wednesday at noon and an XQuery tutorial talk on Thursday morning, stop by and say hi.

Although the weather is pretty good in Dallas (or at least out by the airport) watching the weather channel makes it obvious that everyone wasn't as lucky WRT the storm. My best wishes go out to all the folks affected by the storm(s).

When I'd talk at Ascend about how Visual Studio auto-deploy of SQLCLR assemblies not only catalogs the assemblies and the PDB files to SQL Server 2005 but also the source files, folks would always ask "why the source files"? Today I found out; I'm surprised it escaped me for so long.

In Visual Studio Server Explorer, if you've deployed the source files and PDB, you can right-click on any T-SQL or SQLCLR stored procedure, UDF, etc and and debug it, source code and all, whether or not you have the actual project files in-hand. It will just get them from inside SQL Server. Pretty neat...so that's what its there for.

There is a pretty hefty (but *completely* understandable and necessary) price to pay for admission to this feature, however. You must be sysadmin in SQL Server (CONTROL SERVER) to do this. Understandable because you're going to:
a. stop all managed threads on the server
b. be able to snoop around

So...that's why. And how.

After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now.

In the September CTP version on SQL Server 2005 (I think its probably the last CTP), you need special permissions to CREATE an ASSEMBLY with UNSAFE permission set. You must have either one of the following:

1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
or
2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

We'd written about the second choice. Here's one of the combinations that works:

1. Create a strong named key in c:\temp\assm.snk
2. Sign the assembly unsafe1.dll with this strong named key
3. Make a SQL Server LOGIN for the key.
4. Give LOGIN the appropriate permissions
5. Catalog the unsafe assembly

In code, it looks like this:

-- master key in master database
USE master
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go

-- keyfile generated by VS or .NET command line utilities
CREATE ASYMMETRIC KEY assm FROM FILE='c:\temp\assm.snk'
go

CREATE LOGIN snk FROM ASYMMETRIC KEY assm
go

GRANT UNSAFE ASSEMBLY TO snk
GO

USE somedb
GO

CREATE ASSEMBLY unsafeassemblyex FROM 'c:\temp\unsafe1.dll'
  WITH permission_set = unsafe
GO

That's only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE=...) or an assembly already cataloged inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY...). You can do the same thing with certificates.

So one of the SQLCLR security features we wrote about over a year ago has come to pass.

This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement Service Broker transport security. That is, create a certificate in master, then create a login mapped to the cert. Now use the cert in the CREATE ENDPOINT ... FOR SERVICE_BROKER.

To allow your business partners to authenticate with your endpoint (in their implementation of transport security) you BACKUP the cert to a file (the default is to BACKUP public key portion only) and then send the cert to your business partner. They install it in master to use to authenticate your endpoint.

There a variation of CREATE USER foo FROM CERTIFICATE that's caused a lot of discussion on one of the newsgroups lately. That is "CREATE USER ... WITHOUT LOGIN". This is used with Service Broker too, but with full dialog security, rather than transport security. This USER has only the permissions it needs (usually only SEND on the SERVICE/ or RECEIVE on the QUEUE in question). So this is used for the following scenario:

1. Create a user without a login
2. Create a cert owned by the user
3. Backup the cert (public key only) to a file, send to business partner
4. They create a user without login
5. They install your cert from a file
6. They GRANT the user the appropriate access
7. Your BEGIN DIALOG uses full dialog security (ENCRYPTION = ON is the default).

Folks on newsgroups have said it (USER WITHOUT LOGIN) is undoc'd, but I remembered seeing this before. Then I remembered where. It's in Neils' remote Service Broker examples here.

You've probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in "CREATE PROCEDURE foo WITH EXECUTE AS OWNER". But EXECUTE AS can also be used at a session level,like this:

EXECUTE AS USER='fred'
-- some T-SQL here
REVERT

This is meant to replace the SETUSER verb because you might want things executed as a specfic login as well as user. So there's also EXECUTE AS LOGIN='freds_login'. You can aribtrarily nest EXECUTE AS blocks and there's subject to the nice, granular, IMPERSONATE privilege. Originally, I'd heard there was going to be a "REVERT ALL", but there's security mischief that might happen with this, so it never happened.

BUT.
There are two variations of EXECUTE AS that are interesting, based on security considerations. First one is "EXECUTE AS USER='fred' WITH NO REVERT". Exactly what it sounds like.

Next one is EXECUTE AS WITH COOKIE. The idea is similar to what I described in sp_unsetapprole. You don't want just any ol' level of impersonation to do REVERT. So EXECUTE..WITH COOKIE gives you a cookie to use with REVERT.

As a blog reader as well as blog writer, I'm disturbed by a trend that seems to be more commonplace lately, the “continued here” trend. I'll bet you've seen it too. The blog entry is comprised of two sentences or so (or even worse, just a teaser line) followed by “continued here” (or “more at” or something).

This is irritating because I'm subscribing to lots of blogs, nowadays, really too many to read continuously. So I'll browse 'em, leave 'em in mail folders (using Newsgator) and attempt to actually read them on planes. Well, “continued here” doesn't work without a network connection. Bummer. Was this invented for folks with short attention spans or (hopefully) just to get around limited bandwidth issues?

On a more constructive note, does anyone know how to make Newsgator (or any other blog reader) follow the “continued here”s and download them? I'm tempted to simply unsubscribe to these folks' blogs. Or is there simply some way to turn “continued here” off on a subscription by subscription basis? I must be missing something.

One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is.

You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an alternate form of sp_setapprole. It looks like this:

sp_addapprole 'myapp', 'StrongPW1'
GO

DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
     @fCreateCookie = true, @cookie = @theCookie OUTPUT
-- Check user, should be myapp
SELECT USER_NAME()
-- now, unset it
EXEC sp_unsetapprole @theCookie
-- Check user should be original user
SELECT USER_NAME()
GO

So does it now support connection pooling with ADO.NET or OLE DB/ODBC? I'd think the capability exists, but because this is a recent feature addition and they'd have to store the cookie in the pooling code somewhere, to use with sp_reset_connection, I don't think its built in yet. But, if you remember to store the cookie and unset it yourself...

In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of:

EncryptBy (Key/Certificate/PassPhrase) and
DecryptBy (Key/Certificate/PassPhrase)

But there's also the less well-known:

SignByAsymKey/SignByCert
VerifySignedByAsymKey/VerifySignedByCert
HashBytes

and also the aptly-named: DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey

The "auto" versions are for convenience. When you're doing encryption or decryption, you have to remember to open the keys that have been used to encrypt the data you want to read. And to close them when you're done. The "Auto" versions do this for you, when you're decrypting, so you can provide a decrypting VIEW, for example. I'll have more to say about this soon.

While working on the new edition of our book for after RTM, I've been looking around for security features that I missed or that didn't exist when we wrote it (May 2004 and before). Here's one:

In SQL Server 2005, you can DISABLE a LOGIN by using ALTER LOGIN. You can also change the name of any LOGIN (including 'sa'), making things more like Windows security. You would change the name of the sa account to give hackers one more item to guess. Here's how.

ALTER LOGIN sa WITH NAME = fred
go

-- fred (sa) on vacation
ALTER LOGIN fred DISABLE
go

-- When the sa returns from vacation
ALTER LOGIN fred ENABLE
go

That's the question that folks ask a lot lately. See you at PDC?

It's not that I wouldn't *like* to go. It's that there's another 3-letter acronym in my life that week, OSF. No, that's not the Open Software Foundation, it's the Oregon Shakespeare Festival. Mary and I will be making our twice-yearly trek to Ashland to see some plays. We'll be seeing Twelfth Night and Loves Labor's Lost among others. They do Shakespeare and more, there's lots of contemporary plays. And they're so popular that to get good seats we buy 'em a year in advance.

And Microsoft never checks with me before they schedule these things....oh well. Two years ago, I missed the very first Ascend teach for OSF and I remember Dan (who taught it) writing an XML data type demo and an ObjectSpaces lab, using XML version of Shakespeare's plays as test data, just to be topical.

I will still be at SQLPass summit in Texas at the end of September. And Tech*Ed Hong Kong the week after. Doing pre-conference talks about...what else...SQL Server 2005 new features. And other talks (about SQL Server and about ADO.NET 2.0) as well. If you're in either neighborhood drop by. You can let me know what I missed at PDC. SQLskills will be well represented though, Kimberly will be there.