Notes from the TechNet Events in Spokane and Redmond, WA

Hello Washington!  Thanks again for a wonderful visit to your towns, and a chance to meet you.

As promised, here is my posting with answers to the questions I wrote down.  I hope you find them as useful as I will.

—

Q: Does the Upgrade Advisor do a check on passwords used for SQL logins against current domain or local password account policy settings?

A: I can’t find anything in the documentation for the upgrade advisor about password policy checking or flagging blank passwords, so it isn’t a feature of the beta version tool.  However, I have learned that they are still adding rules to the tool, so it may still be in the final version.

Q: “Execute AS” is cool. Being able to set the context as another user without that user’s password seems to be a security hole. Can it be used in malicious ways? Can someone set the context as someone else and use it in ways ordinarily not allowed?

A:  If you’ll recall, in the demonstration I was logged in and running as the Administrator.  I wanted to view objects and operate in the context of the MarketingUser, who had permissions only to view objects on the Northwind database. 

I found a really good article (actually a great SERIES of articles) on SQL Server 2005 Security and Authorization by Marcin Policht.  In this article he outlines the various ways to use EXECUTE AS.  Here’s the portion that answers your question (bold is my own) on how security is maintained:

EXECUTE AS USER='user_name' (or 'login_name') - assigns the security context of a specific database user for the duration of the module execution. This means that permissions on all referenced objects are evaluated against this user_name, rather than against the original caller (regardless of whether the module and objects have the same ownership or not). This value can be applied to functions, stored procedures, both data modification language and data definition language (introduced in SQL Server 2005) triggers with database scope, as well as queues. 'login_name' (designating a valid SQL Server 2005 login) is applicable to data definition language triggers with server scope. Note that in order for the module to work as intended, its creator needs to either be a member of SysAdmin server role, db_owner database role, or have Impresonate permissions on the user account whose security context is used.

So you can’t be just anybody and use the EXECUTE AS statement.

Q: Does the query editor include a debugger?

A: No, apparently it does not.  According to this pre-release article – if you want to debug T-SQL, you’ll use Visual Studio:

In the past, T-SQL developers often wrote and debugged their T-SQL applications using a SQL Server tool called Query Analyzer. Today, however, the SQL Server Management Console, which is the successor to Query Analyzer, does not provide a debugger, so T-SQL developers must use the Visual Studio 2005 debugger.

Developers can also write their T-SQL applications in Visual Studio 2005, using a Database Project.

In either case, debugging procedures are almost identical.

Q: Peer-to-Peer Replication is cool. Mirroring is Cool. What if I configure a Mirror, and have the Principle server in the mirror participate in a Peer-to-Peer replication also. If the mirror goes down, will the Mirror server take over in the Peer-to-peer relationship?

A: My brain hurts!  (apologies to Python, Monty Ltd.)

Remember we talked about how Replication is configured at the server level, and we set up distributors and then publish articles.  Peer-to-peer replication is really just a push-replication relationship in two directions. 

Mirroring is configured at the database level.  We’re taking database and making sure we have a failover.  Remember that the Mirror, once configured and enabled as such, is not able to do ANYTHING unless it is called upon to become the principle.  So if we were to set up the peer-to-peer (to peer) relationship between THREE servers, and then turn on mirroring, we’d have errors in our peer relationship.

What if we configured the mirror first?  Well, again, how would one peer know that it’s supposed to now start pushing to the other server?

So my answer remains the same: No.  I don’t think this is doable.  If someone wants to tackle this as a project to try out, be my guest. 

—

If you have a followup question or comment, feel free to enter it by clicking the comment link below.