Security in SQL Server 2005: Webcast 2-20-2006

Thank you for attending the web cast on SQL Server 2005 Security, as promised here is the scrubbed Q/A log (you kept Kai very busy!).  Please comment if you need more information.  I mentioned a great web site where you can down load the slides and individual demos for this web cast take a look at the session home page: https://www.microsoft.com/technet/community/events/sql2005/sql-05.mspx

Also if you can watch the stream of the web cast here: https://www.microsoft.com/events/EventDetails.aspx?CMTYSvcSource=MSCOMMedia&Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22ID%22+Value%3d%221032289133%22%2f%5e%7earg+Name%3d%22ProviderID%22+Value%3d%22A6B43178-497C-4225-BA42-DF595171F04C%22%2f%5e%7earg+Name%3d%22lang%22+Value%3d%22en%22%2f%5e%7earg+Name%3d%22cr%22+Value%3d%22US%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e

Enjoy!

Question: What is an Endpoint?
Answer: An endpoint is a place where a request or query terminates. These can be HTTP endpoints, for example.

Question: What is a good SQL Server Security website?
Answer: www.sqlsecurity.com

Question: What is a good article for the ten hacker tricks to exploit SQL Server systems?
Answer: https://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1165052_tax301336,00.html?track=NL-464&ad=541873&adg=301324&bucket=ETA

Question: In SAC if you disable or change a setting like, CLR, how do you re-enable it?
Answer: You can turn them on by re-running the SAC or you can turn them on through stored procedure.

Question: Do we need to change our table DDL because of the owner's name?
Answer: You can, but you don't need to. You can add a default schema to future tables.

Question: Can you create certificates using a Windows Certificate Services domain CA through SQL Server, or do you need to go through the Certificates MMC?
Answer: You can use your existing PKI.  You don’t, have to use your SQL Server.

Question: What encryption algorithms are supported by SQL Server 2005?
Answer: SHA (multiple flavors), AES, 3DES, DESX, DES, and I think a few other flavors.

Question: Can SQL Server request certificates through the Domain CA or is it only able to create the self-signed certificates?
Answer: Yes it can request certificates from the Domain CA.

Question: Is there any books for SQL Server 2005 security?
Answer: Not yet.  There is a Pocket Admin Guide for SS2005 that has some information in it. 
Learn more about the Pocket Guide here: https://www.microsoft.com/MSPress/books/6794.asp
I also highly recommend SQL Server 2005 books online: https://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Question: Can you assign resources to developers who'll do a trace to prevent unintended consequences?
Answer: Yes, all you need to run is the ALTER trace statement to give tracing capabilities to the developers.

Question: Is there a white paper with on symmetric key algorithms supported in 2005 in answer to earlier question?
Answer: Yes, there is, Thanks to one of our web cast viewers gave us this excellent document to look at: https://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx#ECAA

Question: Can the built-in certificate authority also be used for SSL with reporting services?
Answer: Yes - https://msdn2.microsoft.com/en-us/library(d=robot)/ms345223.aspx

Question: Where can I find another source for DDL triggers?
Answer: Check out Keith Combs Blahg and specifically look here: https://blogs.technet.com/keithcombs/archive/2005/11/21/414908.aspx

Question: Where do I find a link for system table chart?
Answer: This is a great link to the system tables in SQL Server 2005: https://msdn2.microsoft.com/en-us/library/ms179932.aspx

Question: What are some the scripts that I used for key encryption during the demos?
Answer:  Luckily my good buddy Keith Combs blogged about this.  Take a look at his excellent posting on SQL Data Encryption; this will have samples of the encryption scripts that used: https://blogs.technet.com/keithcombs/archive/2005/11/24/415079.aspx

Question: Is there a "Best Practice in SQL Server 2005 White Paper" available?
Answer: I am not aware of a guide released yet, here is the home page - https://www.microsoft.com/sql/default.mspx

Question: Does the "Execute As" command give any kind of a special log entry, or does this possibly gives the administrator an end run around accountability features you have in place?
Answer: Depending on the situation you could probably use DDL trigger or auditing events to log this.  However, remember with the EXECUTE AS clause, you have to have certain and specific permission on the objects you are calling with the clause.  For example if you needed to execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.  For more information on the execute clause take a look here: https://msdn2.microsoft.com/en-us/library(d=robot)/ms188354.aspx

Question: Can you grant dbo to someone on a database, but deny them access to modify DDL triggers?
Answer: Yes, you can. You just have to have work with the appropriate permissions.

· To drop a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

· To drop a DDL trigger defined with server scope (ON ALL SERVER) requires CONTROL SERVER permission in the server. To drop a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

To learn more about dropping triggers and the DROP trigger statement take a look here: https://msdn2.microsoft.com/en-us/library/ms173497.aspx

Question: What about the rights to run new SSIS packages?
Answer:  SQL Server 2005 provides several new mechanisms for securing SSIS packages—these include package encryption and the digital signing of packages.  To learn more check out the great information here: https://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

Question: Where can I find out more information about upcoming web casts?
Answer: Check out the new webcast interactive calendar - https://www.microsoft.com/events/webcasts/calendar/monthview.aspx

Question: Is there a way to give users access to view SQL Jobs without being SA?
Answer: You can only view jobs that you own, unless you are a member of the sysadmin fixed server role. Members of this role can view all jobs.  So I would give permissions to those users.

Question: Where can I find the posting by James Luetkehoelter's (Jan SQL Standard) "SQL Server Security for the Paranoid DBA”, this is a good place to learn about the caution of using too much encryption.
Answer: A web cast viewer recommended this: https://blog.pinpub.com/archives/sqlblog/2006/02/sql_server_stan.html