Contact

Subscribe via Email

Subscribe via RSS/JSON

Categories

Creative Commons Attribution 4.0 International License
© Rakhesh Sasidharan

Elsewhere

Gaining access to Citrix Studio if you don’t already have access

I am proud of this one. Spent a lot of time working my way through this even though I don’t know much SQL and finally cracked it. Probably not a big deal for any “experts” out there but this pretty much was the highlight of my day. :) 

A colleague of mine setup a new Citrix site and went for holiday, without giving the rest of us admin access to the site. As expected we needed to access it and while we were waiting for him to get in touch to our messages I thought there must be a way to hack into the system. There is a database behind the scene after all, so if I could just get access to that then maybe I can give myself admin access. 

Turns out there is.

We had gone with SQL Express with both delivery controller and SQL server on the same machine, and thanks to this Citrix support article I learnt that in such a case the ‘NT AUTHORITY\NETWORK SERVICE’ account is used to login to the SQL server (that article is a good read for other scenarios too BTW). Cool. I knew I could run something as ‘NT AUTHORITY\NETWORK SERVICE’ using SysInternals PSTools. So I downloaded PSTools to that server, opened a command prompt as admin, and ran the following:

All good so far. Next I downloaded SQL Studio and ran that from the above command prompt. Just type "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe" into the command prompt window. That will give you the login prompt and you can connect (if it asks for any details the server name is “<your server name>\SQLEXPRESS” and authentication is “Windows Authentication”). This worked and I was in! Yay.

Snooping around the various SQL tables I came across [DAS].[Administrators] which looked like it could contain the administrators. Did a right click > “Select Top 1000 Rows” (remember I am no SQL guru) and that opened a new query which I executed … and sure enough I could see the sole admin account of my colleague who’s on holiday. Nice! Seems to be a list of SIDs followed by a UserIdentityType column of value 0 and Enabled column of value 1. Hmm, maybe I can just add to this table and be done with it? Did a bit of Googling on how to insert into a table, found my SID from psgetsid of the PSTools I had already downloaded, and tried the following:

And … that didn’t work! Got the following error: “The INSERT permission was denied on the object ‘Administrators’, database …”

Oh well, worth a shot. I looked around the user accounts on the SQL server and the roles and permissions for the network service account and from what I could see it has all the rights it needs. There’s no other account. So surely that’s what the Delivery Controller too is using to add new admins etc. Time to read more. 

Back to the Citrix support article I came across earlier, I found the same roles that I had found on the SQL server and also this bit: “Each one of the preceding roles has the minimum permissions granted to it to allow the corresponding service on the controller to function. These permissions are restricted to execute on stored procedures and read on some tables.” Ah ha! So it has permissions to only execute stored procedures and that’s obviously how it is adding admins. Cool!

Obviously I have no idea what a stored procedure is, so time to Google again on how to get to that. Did that, and found a ton of them them under Programmability > Stored Procedures. The table was called “DAS” something so upon a hunch I looked around any procedures starting with “DAS” (not entirely a hunch, I noticed that the procedures seemed to start with similar names as the tables so I made a guess that probably the stored procedures for the “DAS” tables would start with the same name). That paid off and I found “DAS.NewAdministrator”. Cool!

Note to anyone else: to see a stored procedure you right click and do “Modify”. That shows you the code. You can run it via right click > “Execute Stored Procedure” which will give a popup to enter the parameters for the procedure. This part stumped me for a while. I entered the parameters as best as I could figure but it kept throwing various errors. That’s when I spent some time looking at the procedure code and cracked the problem. Once you enter the parameters SQL Studio generates a query which you execute, and that was giving errors. I figured the issue and modified the query. It looks like the below in case anyone else wants to copy-paste and modify:

And that worked! Whoo hoo. Still can’t access via Studio, but I double checked the [DAS].[Administrators] table and my account was there. 

Hmm, maybe the issue is that I have added myself as an admin but I haven’t granted myself any rights. Remember when you do this via the Studio you have to select a scope and also what rights you want to assign? Probably got to do that via SQL! Not a problem, back to Google. :)

I came across another Citrix article (why didn’t I just find this the first time!? it tackles pretty much what I am doing here. anyways, the first few steps of that article are incorrect as that’s what I too had tried and it didn’t work for me … so good I didn’t stumble upon this initially). This one showed me how to give my admin account rights and scope. Here’s the additional SQL you need to run:

No rocket science here. It uses another stored procedure called “DAS.AddRight” to give my SID “Full Administrator” rights to the scope of “All Objects”. That completed without any errors, so I closed and opened Citrix Studio and yay I am now in!

And that, ladies and gentlemen is how you get into Citrix Studio if you don’t already have access! :)

Service SIDs etc.

Just so I don’t forget. 

The SCOM Agent on a server is called “Microsoft Monitoring Agent”. The short service name is “HealthService” and is set to run as Local System (NT Authority\System). Although not used by default, this service also has a virtual account created automatically by Windows called “NT SERVICE\HealthService” (this was a change introduced in Server 2008). 

As a refresher to myself and any others – this is a virtual account. – i.e. a local account managed by Windows and one which we don’t have much control over (like change the password etc). All services, even though they may be set to run under Local System can also run in a restricted mode under an automatically created virtual account “NT Service\<ServiceName>”. As with Local System, when a service running under such an account accesses a remote system it does so using the credentials of the machine it is running on – i.e. “<DomainName>\<ComputerName>$“.

Since these virtual accounts correspond to a service, and each virtual account has a unique SID, such virtual accounts are also called service SIDs. 

Although all services have a virtual account, it is not used by default. To see whether a virtual account is used or not one can use the sc qsidtype command. This queries the type of the SID of the virtual account. 

A type of NONE as in the above case means this virtual account is not used by the service. If we want a service to use its virtual account we must change this type to “Unrestricted” (or one could set it to “Restricted” too which creates a “write restricted” token – see this and this post to understand what that means). 

The sc sidtype command can be used to change this. 

A service SID is of the form S-1-5-80-{SHA1 hash of short service name}. You can find this via the sc showsid command too:

Note the status “Active”? That’s because I ran the above command after changing the SID type to “Unrestricted”. Before that, when the service SID wasn’t being used, the status was “Inactive”. 

So why am I reading about service SIDs now? :) It’s because I am playing with SCOM and as part of adding one of our SQL servers to it for monitoring I started getting alerts like these:

I figured this would be because the account under which the Monitoring Agent runs has no permissions to the SQL databases, so I looked at RunAs accounts for SQL and came across this blog post. Apparently the in thing nowadays is to change the Monitoring Agent to use a service SID and give that service SID access to the databases. Neat, eh! :)

I did the first step above – changing the SID type to “Unrestricted” so the Monitoring Agent uses that service SID. So next step is to give it access to the databases. This can be done by executing the following in SQL Management Studio after connecting to the SQL server in question:

The comments explain what it does. And yes, it gives the “NT Service\HealthService” service SID admin rights to the server. I got this code snippet from this KB article but the original blog post I was reading has a version which gives minimal rights (it has some other cool goodies too, like a task to create this automatically). I was ok giving this service SID admin rights. 

ODBC DSN creation for vCenter database

Was creating a DSN on my vCenter 6.5 to point to a SQL 2014 database and the following post was useful. Take special note of the path from where you can install the SQL driver for the DSN.

 

P2V a SQL cluster by breaking the cluster

Need to P2V a SQL cluster at work. Here’s screenshots of what I did in a test environment to see if an idea of mine would work.

We have a 2 physical-nodes SQL cluster. The requirement was to convert this into a single virtual machine.

P2V-ing a single server is easy. Use VMware Converter. But P2V-ing a cluster like this is tricky. You could P2V each node and end up with a cluster of 2 virtual-nodes but that wasn’t what we wanted. We didn’t want to deal with RDMs and such for the cluster, so we wanted to get rid of the cluster itself. VMware can provide HA if anything happens to the single node.

My idea was to break the cluster and get one of the nodes of the cluster to assume the identity of the cluster. Have SQL running off that. Virtualize this single node. And since there’s no change as far as the outside world is concerned no one’s the wiser.

Found a blog post that pretty much does what I had in mind. Found one more which was useful but didn’t really pertain to my situation. Have a look at the latter post if your DTC is on the Quorum drive (wasn’t so in my case).

So here we go.

1) Make the node that I want to retain as the active node of the cluster (so it was all the disks and databases). Then shutdown SQL server.

sqlshutdown

2) Shutdown the cluster.

clustershutdown

3) Remove the node we want to retain, from the cluster.

We can’t remove/ evict the node via GUI as the cluster is offline. Nor can we remove the Failover Cluster feature from the node as it is still part of a cluster (even though the cluster is shutdown). So we need to do a bit or “surgery”. :)

Open PowerShell and do the following:

This simply clears any cluster related configuration from the node. It is meant to be used on evicted nodes.

Once that’s done remove the Failover Cluster feature and reboot the node. If you want to do this via PowerShell:

4) Bring online the previously shared disks.

Once the node is up and running, open Disk Management and mark as online the shared disks that were previously part of the cluster.

disksonline

5) Change the IP and name of this node to that of the cluster.

Straight-forward. Add CNAME entries in DNS if required. Also, you will have to remove the cluster computer object from AD first before renaming this node to that name.

6) Make some registry changes.

The SQL Server is still not running as it expects to be on a cluster. So make some registry changes.

First go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup and open the entry called SQLCluster and change its value from 1 to 0.

Then take a backup (just in case; we don’t really need it) of the key called HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Cluster and delete it.

Note that MSSQL10_50.MSSQLSERVER may vary depending on whether you have a different version of SQL than in my case.

7) Start the SQL services and change their startup type to Automatic.

I had 3 services.

Now your SQL server should be working.

8) Restart the server – not needed, but I did so anyways.

Test?

If you are doing this in a test environment (like I was) and don’t have any SQL applications to test with, do the following.

Right click the desktop on any computer (or the SQL server computer itself) and create a new text file. Then rename that to blah.udl. The name doesn’t matter as long as the extension is .udl. Double click on that to get a window like this:

udl

Now you can fill in the SQL server name and test it.

One thing to keep in mind (if you are not a SQL person – I am not). The Windows NT Integrated security is what you need to use if you want to authenticate against the server with an AD account. It is tempting to select the “Use a specific user name …” option and put in an AD username/ password there, but that won’t work. That option is for using SQL authentication.

If you want to use a different AD account you will have to do a run as of the tool.

Also, on a fresh install of SQL server SQL authentication is disabled by default. You can create SQL accounts but authentication will fail. To enable SQL authentication right click on the server in SQL Server Management Studio and go to Properties, then go to Security and enable SQL authentication.

sqlauth

That’s all!

Now one can P2V this node.