Tag: syndicated

  • Brian Kelley on Security at SQL Server Connections

    Random notes from Brian Kelley’s security talk. Brian spent a number of years working a a security guy for a bank in the Windows and infrastructure teams.

    Networking, the UDP port is 1434. This is the listener and browser for SQL.

    Default Tcp1433 is the default port for SQL. What about TCP 2433? In SQL Server 2000, the “hide my server” button moved the listener to 2433.

    If you are a worthwhile target, a hacker might take weeks to dig through the access points for your servers.

    Those hallways conversations matter. You never know when someone is actively listening to get information about your internal systems. Tom Clancy is a perfect example. “The Hunt for Red October” contained a lot of classified information about submarines, Mr. Clancy was questioned by the CIA to determine how he learned things. He did a lot of research, listened a lot around personnel and put various pieces of information together to understand how submarines work,

    These are hacking tools and you should use them with caution and with permission in your organization. use them carefully.
    Nmap is a port scanner.
    Quest’s Discovery Wizard – will find sql servers
    SQL Ping – similar to the Discovery Wizard
    Perl, Python, PowerShell scripting
    Nessus and other scanners

    What do you check? Blank sa or simple sa password. Using “sa”, “dba”, “password” or anything like this is a problem, once someone can get into one server, they often jump to another one,

    What was the last SQL server vulnerability? I didn’t remember, but Brian told us it was MS09-004, which affected SQL Server 2000 SP4 and SQL Server 2005 SP2. It allowed access through a replication extended stored procedure.

    People hop from server to server, so it’s a good reason to have separate service accounts for each instance.

    The standard hacker methodology is to find a weak point, attach, and then expand your attack. Move from machine to machine, or grow to more privileged accounts. This is why you need to provide a series of barriers, enforce auditing, and be diligent on vulnerabilities.

    Third party apps are a problem, often with weak passwords, sometimes stored on the file system, if someone can get that file, they can attack your sql server.

    Lots of scary stuff. It paints a picture that makes it seem like you cannot protect your serveres. After 45 minutes of scary ways to attack, Brian goes into a series of ways to protect your servers. Firewall your servers at the network layer, and remember to cover domain controllers, backup servers, dns servers, and other servers that your sql server might talk to.

    A intrusion detection system behind your firewall is a good idea.

    Most issues come from user workstations. Limit the accesss their systems have to the server, remove netbios, file shares, etc.

    Pen test your systems. Run real world tests. Watch out for maintenance crews, cleaning crews, weekend open doors, etc. Watch out for backup tapes. They are a source of vulnerability. Data center tours are an issue, if you have a large data center.

    Watch out for open sessions, remaining logged in, your password is in memory and if someone can get to the server, they can get logged in users’ passwords.

    SQL Server and Windows 2008 are much more secure. Many of the known vulnerabilities from the past will not work. A good reason to upgrade past 2003/2005.

    Overall i think that a good firewall protects most of your systems, but there is s lot of education needed among both administrators and non IT people to keep secrets secret, be aware of conversations away from your office, and dont take shortcuts, most security isn’t hard. It might be a pain, but often putting up with a little inconvenience can greatly increase your security.

  • Extended Events at SQL Server Connections with Jonathan Kehayias

    I was very interested to hear Jonathan Kehayias’ session on extended events. I know this is one of the areas i need work and it is becoming a more important part of SQL Server with each version. Beginning with a good discussion of what extended events are and the terminology is, Jonathan presented a lot of information. Much like PBM, this subsystem requires you to learn a lot of new names and terms in order to even understand the documentation.

    With a dozen or so terms I am sure i wasn’t the only one that was slightly confused, not because of the explanations but just because it’s a lot of information. I know i need to read up on this a bit more before i see another session.

    One important note: You Need to learn XML. all of the results come back as xml and some knowledge of xquery is going to be necessary to work with the event results, not my favorite thing, but i have another skill to work on now.

    After 20 minutes, it’s demo time. The first questions is do log operations cause waits? We see a demo that shows what is recorded in DMVs, what the events see, and what is reported in the query statistics.

    An interesting fact. Once the event data is gathered, you cannot drop the session without losing the data. You can however drop the events and leave the session so you can query the data.

    Another item, causality tracking is needed to associate events to a specific process. It adds overhead, but you need this at times to determine which events correspond to which particular sessions doing work.

    How often does ghost cleanup run and what does it do? Demo 2 looks at the events that are associated with ghost cleanup. In a busy system, ghost cleanup can become a bottleneck, so it can be handy to know if this is a problem. There is a trace flag to turn this off, but it turns it off for the instance.

    The demo looks at what happens when you delete a number of records and the ghost cleanup task begins to fire. By turning off and on ghost cleanup, we can see records being deleted. Lots of events, and we need ghost cleanup hitting lots of records in a short period of time. In fact we saw 12 rows being cleaned up in one ms.

    The last demo is a look at checkpoint. What happens with a checkpoint? When does it run during a backup? During the demo we can see the checkpoint being started right after the backup starts and we can see lots of pages being flushed to disk.

    Very cool demos. Lots more information than you normally want, but useful stuff for digging into problematic systems.

  • Brian Kelley talking Windows Internals for SQL Pros at DevConnections

    I have known Brian for nearly as long as i have been running SQLServerCentral. He has been the guy that I called for security questions. He was a DBA, then became a security and auditing guy, and is now back to being a DBA. This talk is designed to better explain the host OS for SQL Server pros. Some random notes since i am off to lunch.

    We have user mode and kernel mode. Where does SQL Server run? Most services run in user mode. Kernel mode is for privileged mode, base OS services, memory access, device drivers,, HAL, etc. Most crashes come from device drivers or video drivers, so be sure these are up to date.

    If SQL Server crashes, and it could, you don’t want the OS to crash. You want a graceful recovery or restart. That is why SQL Server runs in user mode.

    Most processes start in user mode. If it makes a call to a system function, the kernel traps the request and switches to kernel mode, the call is carried out, and then the OS switches the process back to user mode. This tries to prevent poorly written applications from harming the OS environment.

    BSOD. Microsoft doesn’t like this terminology and i am not surprised. When this happens, you have a hard OS crash. However you get a crash dump and information. The code listed, the parameters, and the dump file can be important to finding the root cause of the issue. Microsoft typically uses the mini-dump these days of the kernel to debug the issue.

    SQL Server can be run as a command line application, but it typically runs as a server. The services subsystem in Windows manages this. There are various services host that control the services. Your interactions with services, starting, stopping, etc, are with these hosts, not the actual program. The services hosts must communicate with the actual program to have it perform an action.

    Most services are also command line apps because they cannot have on screen interactions. MAPI was an example of this causing problems with SQL Server when it would pop a message at times that required an interactive desktop.

    Windows 2008 (and later) and Vista (and later) have service isolation to prevent service hopping of service accounts. These OS’s also have a delayed auto start to allow some services to be delayed at startup for places where there are dependencies. Reporting services is an example here, you might want to delay this so that the database engine has a chance to start.

    Sql manages its own threads, which are not linked to connections. So each connection is not a thread. Usually more connections than threads. SQL Server manages the threads and while you can boost the maximum number, you ought to do this only after extensive testing or advice from CSS. Or both.

    Threads have priority, determining which threads may run before other threads, there are 32 priority levels. 1-15 are normal, and 16-31 are realtime processes, most things ought not run as 16 or above, SQL Server normally runs at 7, but can be boosted to 13. This is generally not recommended for most systems, but it can be an option. Often if SQL Server is waiting on something, it is usually disk or another bottleneck. Changing thread priority will not necessarily help.

    Affinity mask determines which processors is SQL Server allowed to run on. Tis might be something you use if you have multiple instances and they compete for CPU time.

    Highest priority thread always runs first. The OS an boost priority to prevent thread starvation, but the highest priority runs. Threads run for the duration of its quantum (duration). It can end early because it finishes, it needs to wait in something, or it is pre-empted.

    Why are client tools slow on the server? The quantum duration is lower on servers. The server OS is optimized for background tasks, not interactive tasks. Some tasks get higher boosts. Sound application gets a very high boost, so do not run things like Media Player on a server.