Voice of the DBA

Tag: presentations

  • The Encryption Primer

    This talk deals with SQL Server encryption options. I have given a few variations, and you can get the different decks below.

    SQL Server has a number of encryption features that allow you to better secure your data. This session will examine the basics of encryption and cover the various ways in which you can encode and decode your data to protect it from unauthorized access. Cell level encryption, Transparent Data Encryption, and backup encryption will all be discussed. This session is designed for those who want to learn the basics of how to protect their data.

    This talk looks at the encryption options in SQL Server that are available, including the changes in SQL Server 2012. The agenda is:

    • What is Encryption
    • Encryption in SQL Server
    • Transparent Data Encryption
    • Hashing
    • Symmetric Keys
    • Asymmetric Keys
    • Certificates
    • SSL Communications

    The presentation features basic demos of how these features are implemented in SQL Server.

    Level: 200

    Length: 60-75 minutes

    Demo code:

    Slides:

    Presentation Schedule

    You can view the my speaking schedule here. The upcoming and past deliveries of this session are:

    1. Apr 27, 2013 – SQL Saturday #175 – Fargo
    2. Apr 10-12, 2013 – SQL Intersection, Las Vegas, NV
    3. Apr 9, 2013 – SQL Saturday #197
    4. Mar 9, 2013 – SQL Saturday #187
    5. Mar 7, 2013 – Richmond SQL Server User Group
    6. Nov 15, 2012 – Denver SQL Server Users Group
    7. Nov 13, 2012 – Boulder SQL Server Users Group
    8. Nov 5, 2012 – SQL in the City Seattle, WA 2012
    9. July 28, 2012 – SQL Saturday #144 Sacramento, CA
    10. June 9, 2012 – SQL Saturday #132 – Pensacola
    11. Apr 28, 2012 – SQL Saturday #131 – Phoenix
    12. Mar 26-29 – SQL Connections, Spring 2012

    Related Blog Posts

  • Unstructured Data in SQL Server

    Abstract:

    More and more of our data does not fit neatly into a structured, relational model of rows and columns of data. In this session, you will learn about how SQL Server stores unstructured data, with a special emphasis on how to use Filestream, which integrates SQL Server with the NTFS file system by storing varbinary(max) binary large object data as files stored within the file system. You will also learn about the new SQL Server 2012 filetable feature, which builds on Filestream and provides the ability to read, write, and update Filestream objects directly through the file system. This session is designed for DBAs and developers who need to learn how to manage large quantities of unstructured data.

    This covers SQL Server 2008, R2, and 2012. The basic Agenda:

    • What is unstructured data
    • Filestream
    • FileTable

    There are demos that look at how Filestream works and how FileTable can be used in SQL Server 2012.

    Level: 200

    Length: 60 Minutes

    Demo code: UnstructuredData.zip

    Slides: UnstructuredData.ppt

    Related Posts:

    Presentations:

    You can view my speaking schedule here: http://wp.me/P14wgJ-1tV

  • Quick Recovery Techniques Webinar

    The slides for the SQLServerCentral webinar #13 are available here on the blog. Download them from the link below

    Quick Recovery Techniques PPT

    There aren’t notes in there, but if you have questions, let me know. The recording of the webinar will be up in the Training section of SQLServerCentral next week.

  • Map a Login – Basic Skill #3

    This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

    I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

    Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

    Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.

    loginmap

    Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

    In this case, I have access to my default database, and I can check on my user credentials with this code:

    SELECT USER_NAME()

    This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

    My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function. 

    SELECT SYSTEM_USER

    This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.

    loginmap2

    The Mapping

    We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.

    loginmap3

    This will bring up a dialog for the user, and at the top you can see the login mapped to this user:

    loginmap4

    If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.

    loginmap5

    In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

    I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.