TryCatchFinally.net Some SQL, some .NET, and whatever else

14Jan/150

Querying Active Directory from SQL Server

SQL Server provides some pretty flexible integration with Active Directory through the ADSI Linked Server provider, something that's present by default when you install SQL Server. If you've never used it before, it allows you to connect to a domain controller and query AD the same way you'd query any other linked server. For example, it gives you the option to:

  • Identify when logins to SQL Servers or databases that support financial applications exist, but have no matching AD account (either direct integrated logins, or if SQL logins or rows in a "User" table have been set up to match the AD login)
  • Kick off alerts to provision the user in various systems based on their AD group membership
  • Automatically trigger an action when a new account appears in active directory (for example, we auto-provision security badges and send an email alert to our head of security to assign the appropriate rights)

While much of this could also be done from Powershell as well, we use the SQL Server Agent to manage many of our scheduled job (because it's so handy to have the agent remotely accessible), as well as sometimes just needing data from AD in a query. To support a number of processes we have in place, we run a synchronization job every so often throughout the day that pulls about two dozen fields for all users and synchronizes them into a table if anything has changed.

Setting up the linked server itself is pretty straightforward (courtesy of http://community.spiceworks.com/how_to/show/27494-create-a-sql-linked-server-to-adsi):

  1. Create the linked server itself
  2. Set the security context (if you want to query AD as something other than the SQL Server Service account - by default, all domain users can do this and it's only required if the domain is remote or if, for some reason, your SQL Service account's AD rights have been restricted, like if you're running as "LOCAL SERVICE")
  3. Enable OPENQUERY (Ad Hoc Distributed Queries)

You'll notice that setting up the linked server itself doesn't actually specify where Active Directory is located or what domain/forest you'll be querying - that's actually done in the query itself. In each query, you'll need to specify the FQDN (Fully-qualified domain name) of the domain (or OU) of the domain you're querying. For example, we'd get all users from a domain by issuing the following query (in this example, "ADLinkedServerName" is the linked server we just created, and our domain is "corp.mycompany.local"):

SELECT EmployeeNumber, Name AS FullName, givenName as FirstName, sn as LastName,
L AS Location, samAccountName as ADAccount
FROM OPENQUERY(ADLinkedServerName,'SELECT Name, L, givenName, sn,
EmployeeNumber, EmployeeID,samAccountName,createtimestamp
FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
WHERE objectClass =''user''') ad

This query will search that OU ("Users", in this case) and everything below it, so changing the FROM to "LDAP://DC=corp,DC=mycompany,DC=local" would fetch the entire directory (for all the "user" objects), regardless of what folder they appeared it - if your directory puts users in another OU (like "Associates", for example), you should adjust the query accordingly.

For column names, you can pull any AD properties at all that you’re looking for – even custom ones that aren't part of a standard AD configuration. To get an easy list of AD properties to choose from, I like using ADSIEDIT (part of Microsoft’s Remote Server Administration Tools - download RSAT for Windows 7 or RSAT for Windows 8.1) – just drill down all the way down to an object, like a user, right click on them and select “Properties”, and you can see a list of all the properties on that account. If you’ve got Domain Admin rights, this tool can be used to modify these values too, but for querying, you only need to be a domain user or somebody who has rights to browse AD. Make a note of the names of particular properties that you're interested in - also note that AD queries are case-sensitive, so you'll need to note the casing of these properties as well.

One potential gotcha that I've run into is that maximum result size that AD will return in a single query can be set as part of domain policy - by default it's 1000 records at once, and can be configured by setting or adjusting the "PageSize" property on your domain controllers (see https://support.microsoft.com/kb/315071/en-us). Also, there's a "MaxResultSetSize" property as well that's set to 256KB by default, but I've never hit it - unless you're pulling every single property back, you'd likely hit the PageSize row limit before you hit the ResultSize byte limit, but remember that both are there. If you do hit the AD result count limit, it will return the rows up to the limit, but then execution stops with a kind of cryptic error:

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "YOURDOMAIN".

If your domain is larger than the PageSize limit, you'll need to cut your query into multiple return sets of data so you don't exceed the limit on any single query. Since our domain contains about 2400 users, we were able to do it in two queries, broken up like this:

SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName<''L''') as c
UNION ALL
SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName>=''L''') as c

By dividing the names on L, this cut the directory roughly in half - if yours was larger, you could divide it by querying each OU separately, or by looping through letters of the alphabet, or whatever makes sense in your setting. You could even do something dynamic like pull as many records as you can, then grab the value from the last record you pulled and use it as the baseline to pull the next set as far as you can, and then repeat until you run out of records. Linked servers don’t allow you to dynamically assemble your query at run-time – it has to be hard-coded in the query – but there are some ways around that (like building your OPENQUERY as a string and then executing it via sp_executesql, for example).

Now that you have your AD records stored in a temp table, you can identify new/changed records and merge them into a SQL table you already have ready using an INSERT/UPDATE/DELETE or MERGE statement, or possibly trigger notifications or some other business process.

I hope this is helpful - if you'd like some more detail, please leave a comment and I'm happy to elaborate where it's necessary!

26Sep/120

Cluster terminology – What “Active/Active” actually means

As a follow-up to my last entry (attempting to clear up some Windows Clustering terminology), I've found an article that makes another distinction that I forgot to include - the difference between an active/passive and an active/active cluster:

The misconception of active/active clustering (a la AirborneGeek.com)

The understanding among those new to cluster seems to be that a/a vs. a/p is a licensing question, and then if you're licensed for it, you just turn it on. In reality, it really just describes whether you have clustered services living on only one node or split between both nodes (during normal operation - during a cluster failover, any cluster might be active/active for a short period of time. Or, I suppose, your cluster is active/active if your quorum drive lives on the opposite node from your clustered service). There's no load-balancing involved in clustering at all - at any time, only one node owns a particular resource, and only that node is responding to client requests for that resource.

In SQL Server 2012 AlwaysOn, the new high-availability feature, the SQL Server service is running on both cluster nodes, but client access (through the "Availability Group") is controlled by the cluster service. That means that all clients making a connection go first to the active server, and then the SQL Service there might send them to get their data from one of the other nodes (it's worth reiterating here that, in AlwaysOn, SQL Server isn't clustered, but the SQL services operate independently on each node).

19Sep/120

Clearing up Windows Cluster terminology

I wanted to clear up some terminology around Windows Clusters that seems to cause a bit of confusion. I've stumbled across a few questions on StackOverflow and Experts-Exchange that seem to have some basic confusion around "clustering servers" and "how to install an application to a cluster", and I'm hoping to set a few things straight.

  1. There's really no such thing as a clustered server. Servers can have clustering enabled and configured, but the servers themselves aren't really clustered - they're just set up to enable clustered applications. When servers are part of a cluster, they still do all their thinking on their own, including running their own applications, services, and tasks, without the other servers in the cluster even being aware.
  2. You don't cluster servers, you cluster applications and resources. Once servers have had clustering installed and are configured, you can cluster an application or a resource. This clustering is really just telling the cluster manager that you want it to control which server clients talk to when they want to access the resource. The cluster manager ensures that the application (or service or resource) is running on only one node at any given time, and to the extent it's able, it ensures that it's always running (watching for a failure and bringing the resource online on another node and then directing clients to that node instead).
  3. Applications don't have to be "cluster-aware" to be clustered. I work mostly with SQL Server, which is cluster-aware, but applications you cluster don't need to be. You can cluster any service, or resource on a server by just adding it to the cluster manager - the cluster manager will ensure it only runs on one server at a time, not allowing it to start on other nodes. For example, we use a monitoring tool that runs as a service - we installed the service on each cluster node and then added to the cluster manager - it now can be failed back and forth between nodes as a clustered resource, so it's always online, is failure-resistant, and shares a segment of the HKLM in the registry between nodes - all without being explicitly cluster-aware.
  4. SQL Server doesn't need to be clustered when it's installed on a cluster. While you can install a clustered instance of SQL Server (which automatically registers everything with the cluster manager), you can also install stand-alone instances of SQL Server (or any other application) on a cluster. That's actually how a new feature in SQL 2012 - AlwaysOn - works: You install a non-clustered instance of SQL Server on different cluster nodes, and then you let the cluster manager coordinate client connections to the SQL Servers, but they still operate independantly and replicate their data between each other.

Hopefully this clears things up and doesn't lead to more confusion. When I first started working with clustering, I had the impression that setting up a cluster caused the servers to act as one and share all their resources, but that understanding led to a lot of confusion when it came time to set something up or troubleshoot an issue. With the understanding that "clustered servers" are really just servers with clustered resources, and not actually clustered themselves, hopefully it will simply things!

31Aug/120

Network not working after removing Symantec Endpoint Protection

About a year ago, I made the switch from Symantec Endpoint Protection 12 to Microsoft Forefront on a small network I manage, and the transition was mostly smooth. I noticed a few weeks ago, though, that one of my servers still had SEP installed, though it wasn't running. I uninstalled SEP, thinking it would go smoothly, and on reboot, I lost connectivity to the server. No ping, no remote desktop - nothing.

On rolling over to the server and checking the desktop, the network connectivity was completely dead - I couldn't ping the gateway or browse the internet at all. Using Device Manager to uninstall the network card and rebooting didn't do any good, not did attempting to update drivers. However, in device manager, I could see a "Teefer2" entry for every one of my network connections, and it was set to disabled - this is the Symantec Network Threat Protection driver, and it monitors your network activity. Thinking this could be the problem, I attempted to right-click -> Uninstall each of these, but nothing happened - they didn't uninstall.

Trying to update the drivers on the network card (which said a file was missing) always give me the same error message: "The system cannot find the file specified." It would almost work, up until the very end of the driver wizard, and then that error.

I found an article about running a Symantec tool called "Cleanwipe" that's supposed to remove traces of failed Symantec Endpoint installations. You need an open support ticket to get it from Symantec directly, but if you know the filename, you can Google it and get it from others (note: I don't vouch for the results - you're on your own).

Running that tool didn't solve my problem, though it did delete a number of components that were left. Next, I went through and manually deleted the remnants, following these instructions:

http://www.symantec.com/business/support/index?page=content&id=TECH91038&locale=en_US

That removed everything except the Teefer2 entries in my networking list. After about a dozen random fix attempts from all over the internet and reboots in between, I stumbled on an Experts Exchange question where somebody had posted a solution:

http://www.experts-exchange.com/Hardware/Networking_Hardware/Q_23508822.html

For those without access, the fix is simple - delete a single registry value. It's located under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlNetwork and is called "Config". Don't delete the Network key, and don't just clear this value, delete the value entirely. Once you do, you'll be allowed to delete your network cards and the Teefer2 entries from device manager - remove all the Teerfer2 entries and any network connections that were associated with them, and then reboot. Windows will detect your network cards and let you install drivers, except this time it will work.

About 7 hours of troubleshooting and a dozen reboots, and the fix was to delete a single registry value. The Cleanwipe and the manual removal of SEP might have been required as well, but hopefully this saves you some time.

29Apr/1210

Powershell command to get current sessions on an IIS site

After a Powershell session at SQL Saturday (Phoenix #131) this weekend, I'm now suddenly on the lookout for handy powershell commands. The first one lets you see the number of Active* sessions on your IIS site (* because HTTP is stateless, it's really the number of connections that have been opened recently - not what's currently active, which is likely next to zero. I tried browsing around on a test site and it showed only my one user connected).

To get the currently active user count, here's the powershell:

# Ensure you use the server's actual name, not LOCALHOST, which won't work
$Servername = "Your Server Name"
$Sitename = "Name of your IIS Site"

Get-Counter "\\$ServerName\web service($SiteName)\current connections"
10Dec/110

Launching Microsoft File Transfer Manager

In case you were in the middle of a download from MSDN or another Microsoft site that uses the transfer manager and you've accidentally closed it, you can find it at one of two locations:

If you installed it from an MSI:
x64 - C:\Program Files (x86)\Microsoft File Transfer Manager\TransferMgr.exe
x86 - C:\Program Files\Microsoft File Transfer Manager\TransferMgr.exe

Downloaded automatically in IE (more likely):
%SystemRoot%\Downloaded Program Files\TransferMgr.exe

Mine was hiding in that second location - if you download it directly in IE, it doesn't create a start menu icon, so you're not able to re-launch the tool unless you know the file location.

9Dec/110

Dealing with an exception: “An attempt was made to access a socket in a way forbidden by its access permissions”

I was attempting to bind a remoting listening to a particular port and kept receiving an exception when attempting to bind on the production Windows 2008 R2 server itself - it always worked fine on both my development box and our test server. Here's the exception:

Exception message: An attempt was made to access a socket in a way forbidden by its access permissions
Stack Trace:
   at System.Net.Sockets.Socket.DoBind(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.Bind(EndPoint localEP)
   at System.Net.Sockets.TcpListener.Start(Int32 backlog)
   at System.Runtime.Remoting.Channels.ExclusiveTcpListener.Start(Boolean exclusiveAddressUse)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel.StartListening(Object data)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel..ctor(IDictionary properties, IServerChannelSinkProvider sinkProvider, IAuthorizeRemotingConnection authorizeCallback)

Though Googling gave some suggestions to run the process as an administrator (no effect), the actual problem was that my process was trying to listen on a port that was already taken by another listener. Since the port was already in use, it couldn't bind and I received the exception. Stopping the other process resolved the issue immediately.

Not really a clear error message, since it really had nothing to do with permissions at all, but there you go.

30Nov/110

Clustering walkthrough for SQL Server 2008 on Windows 2008

I recently stumbled across a great walk-through for clustering SQL Server on newer versions of Windows. It's really thorough - everything from setting up iSCSI (in this case, to simulate a shared disk when it's physically attached to one node - not ideal, but lets you test the walk-through), adding the required server roles, preparing the servers, and then a walkthrough of every screen in the SQL installation process. Thanks to the writer of this awesome blog!

http://dbperf.wordpress.com/2010/07/10/walkthrough-cluster-setup-sql-win-2008/

8Sep/110

Slow SQL Server queries over a linked server from Windows Server 2008

We recently ran into an issue where linked server queries where sometimes taking significantly longer than they should have - queries that executed in under a second on the source server took two minutes when executed from a remote SQL Server over a linked server connection. We assumed it was something to do with our Windows Failover Cluster network connections, but it was happening on non-clustered servers as well.

After some digging, we discovered that it happened when the source server was Windows Server 2008 (or 2008 R2), regardless of what OS the remote linked server was running, and the issue wasn't tied to any particular version of SQL Server. As it turns out, it related to everybody's least favorite network setting, TCP Auto-Tuning - disabling it on the affected source servers resolved the problem immediately, and the queries returned instantly again.

To remedy the situation, follow these steps:

  1. On the source SQL Server (the one executing the query, not the linked server), open an elevated command prompt (Right click on CMD.EXE and click "Run as Administrator")
  2. To disable TCP Auto-tuning, run the following command:
    netsh int tcp set global autotuninglevel=disabled
  3. To disable Windows Scaling Heuristics (another feature that can speed up clients, but is no good for servers), run:
    netsh int tcp set heuristics disabled
  4. This will automatically take effect after Windows has the chance to refresh some network communications - you can either wait about an hour, or, if you'd prefer it take effect immediately, restart the SQL Instance. A reboot isn't necessary for this setting to take effect.

I'm not sure why these settings are on by default for servers - they seem to speed up internet browsing and other client-type activities (though there's no shortage of complaints online about them grinding Windows Vista/7 clients to a halt as well), but can cause big problems on servers.