Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: Active Directory

Renaming users

One of our customers are moving from one hosting provider to another, and because of this we have to migrate their databases and stuff. While doing this, they’re also changing their domain name (in Active Directory), and since that wasn’t enough change, all users will have a new naming convention in their usernames. With this in mind I’ve created a SQL script that will take “OldUser” and “NewUser”, look for all columns that have NVARCHAR(128), which is not computed, and update all of them. To make it easier, I’ve disabled the triggers and then enabled them when the query is finished.

SET NOCOUNT ON

DECLARE @OldUser AS NVARCHAR(128)
DECLARE @NewUser AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  @OldUser = 'OldDomain\NordnesVidar',
  @NewUser = 'NewDomain\NordVida'

SELECT
  O.name AS TableName, C.name AS ColumnName
    INTO #columns
    FROM sys.sysobjects AS O
    INNER JOIN sys.syscolumns AS C ON O.id = C.id
    WHERE O.xtype = 'U'
      AND O.name LIKE '[as]tbl%' -- Naming convention in Omega for tables
      AND C.xusertype = 231 --NVARCHAR
      AND C.length = 256 -- 128 (unicode)
      AND C.iscomputed = 0
      AND C.name NOT IN ('Domain')
    ORDER BY O.name, C.name

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @TableName = TableName,
      @ColumnName = ColumnName
        FROM #columns

    PRINT @TableName + ' - ' + @ColumnName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    SET @SQL = 'UPDATE [' + @TableName + '] '
    SET @SQL = @SQL + 'SET [' + @ColumnName + '] = ''' + @NewUser + ''' '
    SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] = ''' + @OldUser + ''''
    EXEC (@SQL)

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    DELETE
        FROM #columns
        WHERE TableName = @TableName
          AND ColumnName = @ColumnName
END

DROP TABLE #columns
Advertisements

Windows Small Business Server Code Name Aurora

You might have noticed that Microsoft released a new preview of its new version of Home Server code named “Vail” yesterday. I haven’t had a chance to have a look it yet, but I have installed the new version of SBS (Small Business Server) which is Home Server’s big brother. It’s very similar to Home Server, which is not a big surprise since it’s basically the same product, but SBS contains a couple of features you won’t see in Home Server. The SBS product focuses on small businesses (which might have something to do with the naming?) with less than 25 employees/users.

When installing, the only thing I was required to type was server name, company name, username, password and product key. Other than this, changing regional settings and time zone was the only things I had to do before everything was up and running. And by everything I mean a server with Active Directory, DNS, File Services and IIS installed. In addition to this, it’s ready for you to add (up to 25) users, (up to 25) computers and manage shared folders. Since this is using AD you of course have the option of creating group policies, logging on with the same account on several computers, but since this is also based on Home Server, you Aurora will make backups of all your computers and monitor core services, low disk space, check that anti-virus is up to date etc. If you’re interested, you can set up email notifications for these types of alerts.

By default Aurora creates a couple of shared folders; one for each user and one shared folder which everyone can access. You can of course create new shared, change them or delete whatever you want there.

I think this is a very nice product for small businesses but also branch offices. A feature you can install is “Branch Cache”, so I guess you can add the SBS as a domain in your big company’s forest in AD. When looking at the screenshots in the product overview you can see a much more detailed dashboard than the one included in this preview. I’m really looking forward to the release, and I’m 99.9% sure I’ll migrate my Home Server over to this product when it’s available!

Home Server

I was asked by one of my co-workers, Jan-Børge, what I recommended for a home server. This lead me to give a little talk about what I’ve done in my home-network yesterday. I’m guessing Jan-Børge isn’t the only one wanting to have a server at home to play with, so here’s my recommendations. I’m recommending two servers; one for plain home-server (backup all your PCs, have one place to put your music, videos and pictures etc) and one virtual-hosting server where you can create several virtual servers to test what ever you want. I’ve done the last one myself, where I’ve now got 5 virtual servers; Active Directory, Exchange, Home Server, Web Server and SQL Server.

First of, you of course need legal Windows licenses. The cheapest way to do this is buying your own TechNet Standard subscription. This costs $199 the first year and $149 the next years. With this you can download and install Windows 7, Office 2010, Windows Server 2008 R2, SQL Server, Exchange +++. You got 10 licenses for each OS. Now, the reason why Microsoft has this offer is for IT pro’s (and developers) to get to know Microsoft technology for testing-purposes, before buying it in the company they’re working in. Most of what I’ve tested is either already in use in Omega, or we’re planning on supporting it in some way.

Home Server Vail

If you’re going to buy a server and install Home Server, buy a 64 bit machine, and install the beta of VAIL (Home Server 2.0). It’s being released later this year, but I’ve used the beta a while now and I haven’t run into any issues yet. For hardware I’d recommend a 64 bit processor. Doesn’t matter how fast it is. The minimum requirements for RAM is 1 GB, but I’d put in 2 GB. Make sure the main board has graphics and network integrated. Other than this, just buy the cheapest you can get. I’ve got 2 x 2TB disks, but how much disk space you want is of course up to you. If you suddenly run low on disk space, just order more and plug it in. You need to click ONE button after installing the new disk(s), and it adds it to both backup-space and shared folders. Pretty neat!

After you’ve installed the server you need to connect all your computers to the server. This is done by visiting http://name-of-your-home-server/connect. After this is done, you’ll get three icons on the desktop, and the server will take backup of your computer every night. It will also check all computers if anti virus is installed and updated, anti-malware protection is on and firewall is on.

By double-clicking the Dashboard, you get to administer your home server. Here you can create new shared folders (defaults are Documents, Music, Pictures, Recorded TV, Users and Videos), do manual backups of your computers, find deleted files from your computers, administer the home server web-site and much more.

Virtual Hosting-Server

If you are going to run several virtual servers on one physical server, you need much RAM and CPU cores. The speed of each core isn’t really that important, but I’d recommend 8 GB RAM and Quad core if you’re having 4-5 virtual servers. I would also recommend big disks, and a powerful PSU, so you can add many disks etc. Other than this, buy the cheapest you get.

When it comes to software, I really recommend Windows Server 2008 R2 with Hyper-V. Managing virtual servers with Hyper-V is like eating ice-cream. It’s very easy, and everyone loves it!

Here you see my virtual servers. If I for some reason needed more RAM for the SQL Server, or maybe more CPUs, I just shut it down, right click, properties, and change memory from 1024 to 4096, and change a dropdown box from 1 CPU to 4 CPUs. Then I just click save and turn it on again. Same goes for hard disks. I can create a new file on the physical server (with the .vhd file extension) and add it to the properties of the server. VERY easy.

Nordnes.Me

A couple of weeks ago I looked in the mirror. What did I see? I saw a guy that used most of his spare time on Xbox games. So, what did I do? Well, I had to find something else to do. What else? Build a new server! Yeah yeah, I could go outside having some fresh air etc., but fresh air also comes through the windows, at least if you open them. ANYHOW! I bought two domains; nordnes.me and nrdn.es and though I’d have some fun. A while back I bought a Quad Core AMD CPU for my HDPC, but I bought a new Intel CPU for that, so the AMD processor with the main board was just lying around without being used. I bought 8 GB RAM for it, and 2 x 2TB disks. I’ve also got a Microsoft TechNet subscription, which allows me to download almost any Microsoft product for free to use in a testing environment. What’s more testing environment than my home network? 😉

Now, everything is up and running and here are the specs.

Physical server
vs – Hyper-V Server

Virtual servers, all running with 1GB RAM (2-4 while setting them up):
vsAD – Active Directory, DNS and similar
vsHS – Home Server 2003 (not running. Will probably be deleted soon)
vsLINUX – Debian. Not running, but installed in case I need to test some Linux-stuff
vsMAIL – Exchange Server 2010 (hosting vidar@nordnes.me)
vsSQL – SQL Server, running 5 instances (2005, 2008, 2008R2, Express etc)
vsVAIL – Home Server VAIL
vsWEB – Web server, hosting Nordnes.me and nrdn.es

Home Server

I’ve learned a whole bunch of stuff in the process of installing and configuring everything. Today I finished moving over to the new home server (VAIL) which based on Windows Server 2008 R2. It’s awesome! One of the new features is that you can stream your videos via web (using Silverlight). Oh, while I’m talking about home server. If you’ve got more than one computer at home (or at the office), you might want to think about buying one. It makes sharing videos, music, backing up your computers and making sure all computers have updated antivirus etc. a whole lot easier. But wait till VAIL is released! VAIL will only run on x64 bit, while the old version only runs on x86.

One thing worth mentioning: DO NOT add your Home Server to AD. You will be able to do it, but it causes so many problems. The reason I wanted to do it was to use the same username and password as on all my other machines (both physical and virtual). Also, joining machines to the home server should be done on a wired network, not on wireless. When joined, you can put them back on wireless without any problems, but I haven’t been able to join any of my computers while on wireless. Have no clue why though, since my wireless router is set up as an AP (Access Point).

Active Directory

The main reason I installed AD is that I wanted to use the same username and password on all computers, but also to play around with group policies. Also, Exchange is pretty meaningless, if not impossible, without AD.

Web

This is the server I’ve played most with. I installed SharePoint, played with Office Web Apps and most recently Nordnes.me and nrdn.es. The last one (nrdn.es) is my own test-project for bit.ly/tinyurl.com-like sites. If you’re interested I can send you the project for this one, but there’s no rocket science here. I’ve also built Nordnes.me using Master pages in ASP.NET. This is actually my first site ever in ASP.NET!

Nordnes.me is just a place where I’ve gathered all my public feeds (blog, twitter etc) into one stream. I’m using a windows service to poll all the feeds every 15 minutes or something and then using LINQ to XML (which btw is AWESOME!) with ASP.NET to display them.

Exchange

Since Omega just moved over to Exchange, I felt I had to get some more knowledge about this. It would be interesting anyways since 99% of our customers use it, so I would probably eventually “have to” build something that communicated with Exchange. Before starting the installation I was convinced that this was just another “next next next next finish” project, and I was right. At least to get it up and running. To get it to send and receive mail took a couple of hours though. I am still not able to connect to Exchange outside my network although testexchangeconnectivity.com says everything is working as it should be. Since it’s working locally it’s very hard for me to test outside my network, so I might have to pick someone up from Microsoft and get them home to see my server-collection 😀

SQL

All SQL geeks with self-respect should have at least 5 instances running at home. I’m only using 2008 R2 at the moment, where I’ve got mainly two databases. One for the tiny URLs (nrdn.es) and one for polling twitter. The reason I’m polling twitter is to gather very much data, so I can start playing with SSAS (SQL Server Analysis Services). There are two arguments for polling twitter for this; it’s public and there’s A LOT of data being generated (by people) every second.

Home servers – Part 2

Now all my servers are set up as I wanted them, running everything in AD (Active Directory) and monitoring it with SCOM (System Center Operations Manager). Well, everything in AD is a bit of an exaggeration, since MS Home Server doesn’t like this. I find it weird, but that might just be me. Anyways, after struggling for some while I removed it from the domain and things started working again 🙂

I’ve also used quite some time to try to install SCMDM (System Center Mobile Device Manager), to be able to add my phone to the domain. After installing all the prerequisites I found out it only supports installing on Windows Server 2003. This really bugs me, since it’s now almost two years since Windows Server 2008 was released, and SP1 for SCMDM was released almost a year ago, which did include support for running with AD 2008, but not running on a 2008 server!

Next, I’ve been trying to get rid of my iPod. Not as in selling it, but using my phone as podcast player. Here Microsoft really have something to learn from Apple. With iPod/iPhone you just plug your device in and it syncs with iTunes. What do you think I have to do to sync my podcasts and/or music with my computer? MANUALLY copy the files over. Yes, I said MANUALLY! Steve Ballmer said in an interview that they were planning to add Zune to Windows Mobile. I hope they soon (Zune?) can get their ass out of their ass, because iPod was first released in Q4 2001. That’s 8 years ago, and Microsoft STILL doesn’t support this. So, because they’re so slow, me and a friend of mine (Erik Skagen Vindenes) have started a little project called PodcastPlayer. It’s not only to solve my issues with syncing, but also to learn new technologies. For instance, I’ve now created my first WCF (Windows Communication Foundation) service and started understanding the great possibilities with WCF compared to normal web services. It’s also a great project to start learning Silverlight and the new features of .NET 4.0. I’ll keep you posted about the project when we’ve got something to show.

Did I mention I bought an Xbox? I’ve never been a gamer (except Transport Tycoon and Trackmania), but the Xbox really found the gamer in me. Add me to Xbox Live and I’ll kick your ass in both FIFA 10 (or FIFA 1.0 as we call it) and Call of Duty Modern Warfare 2! My gamer tag is PolarSirNordnes. In addition to games Xbox is a nice media-center which automatically connected to my home server enabling me to play my movies and music directly from it. I already have a media center PC, where I’m going to test out a new MediaCenter application called XBMC MediaCenter recommended by JanCB, but till then I’ll enjoy the nice UI on the Xbox!

Home servers

As a part of the Christmas presents to myself, I bought some new hardware for my server. I had a main board laying around with a quad core AMD processor. Yes, I hate AMD, but it’s better to use it than just having it laying around.. Anyhow, I bought 8GB RAM and 2x2TB disks for it. I’m running Windows Server 2008 R2 on it with Hyper-V, so it’s running 7 virtual servers, including Active Directory, SQL, Web etc.

Over you can see how good I am in Visio! This is a bit outdated since I’ve now bought a Gbit switch to put in my living room so my HDPC, PS3 and my soon to arrive Xbox will all be connected. Hyper-V has a cool feature which enables me to add an internal network between the servers. This is running on 10 Gbit! It’s used for backup, service-monitoring etc. So, why do I need 7 virtual servers? For testing! I’m generally VERY interested in technology, and this enables me to do almost what ever is possible. For instance, I’ve never had the time to check out SharePoint. I know it’s some kind of web-server-thingy, but that’s about it. Also, I want to check out Exchange 2010 and some cool features related to AD in 2008 R2, including Direct Access. The SQL Server is of course running 6 instances (2005, 2005Express etc). I’ve also installed BizTalk on vsWEB. What’s BizTalk? Have no idea! That’s why I’m doing this 🙂

So, to sum up. I’m going to test out SharePoint, Exchange, BizTalk, System Center Operations Manager, AD and of course play a bit with some SQL features I haven’t used too much time with: Analysis Services and Reporting Services. I’ll hopefully get to blog about my findings, if I’m not TOO exited when getting my Xbox. If there’s something in particular you want me to blog about, please let me know!

Active Directory support in AppFrame

Most of you probably know what AD (Active Directory) is, but for you who don’t, it’s a (LDAP) directory service from Microsoft. This enables you to have all user, group and policy management in one place, instead of on all your servers. Most of the systems engineers in Omega have an AD user, which is a member of the SystemsEngineers group. This group has access to the test servers most system engineers need access to. Before we started using AD, when a new guy got hired, we had to manually go in to every server he needed access to, create a local user and add him to the local administrators-group. Now we just add a new user in AD, add him (or her) as a member of the SystemsEngineers group and he is now able to log on to all the servers he needs to, with the same username and password. Neat?

In AD you also got something that’s called an organization unit (OU). This is used to separate objects in the directory. For example could there be one OUs called Omega, OmegaIAT, and OmegaPS. Under these we would put the users, groups etc associated with those OUs. You can also have nested OUs, which means that in the Omega OU you can have one OU called Users, another called Groups, and a third called Servers. This is just to make things easier to find in the directory.

So, what does this got to do with AppFrame? Most of our customers, and also our selves are using AD, and it would be nice to be able to log on automatically into AppFrame without having to type your username and password, right? We already support this, in some degree. You can add a user to AppFrame just by adding his username. For example “COMPANY\User1”. But, what if you want to have a group in AD where you can add users, and those users would automatically be added in AppFrame? Here is an example of code that you would need. For this example we’ll use the domain “MyCompany.com”. Our domain admin have created a group called “AppFrameUsers” which is placed in the OU Office1, Department1. (Department1 is a sub OU of Office1).

First we need to create a linked server from SQL to AD:

EXEC master.dbo.sp_addlinkedserver
	@server = N'AD1',
	@provider=N'ADSDSOObject'

EXEC master.dbo.sp_addlinkedsrvlogin
	@rmtsrvname=N'AD1',
	@useself=N'False',
	@locallogin=N'JobLogin',
	@rmtuser=N'MyCompany\MyUser',
	@rmtpassword='MyDomainPassword'

We create a server “AD1”, where we link the login (“JobLogin”) that is going to run the script under in the job to our domain user (“MyCompany\MyUser”).

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Domain AS NVARCHAR(20) = 'MyCompany'
DECLARE @TopDomain AS NVARCHAR(6) = 'COM'
DECLARE @OrgUnit AS NVARCHAR(200) = 'OU=Department1,OU=Office1'
DECLARE @GroupName AS NVARCHAR(200) = 'AppFrameUsers'
DECLARE @TemplateUser AS NVARCHAR(128) = 'AppFrameTemplateUser'

SET @SQL = '
	SELECT
	  givenName AS FirstName,
	  sn AS LastName,
	  mail AS EMailAddress,
	  sAMAccountName AS Username,
	  mobile AS MobileNumber
		INTO ##ADUsers
		FROM OPENQUERY(adsi3, ''SELECT givenName, sn, mail, sAMAccountName, mobile
				FROM ''''LDAP://DC=' + @Domain + ',DC=' + @TopDomain + '''''
				WHERE objectCategory= ''''Person''''
				  AND objectClass = ''''user''''
				  AND memberOf = ''''
					CN=' + @GroupName + ',' + @OrgUnit + ',
					DC=' + @Domain + ',
					DC=' + @TopDomain + ''''''')
		WHERE NOT EXISTS (SELECT *
					FROM stbl_System_Users
					WHERE Login = ''' + @Domain  + ''' + sAMAccountName)'

EXEC (@SQL)

WHILE EXISTS (SELECT * FROM ##ADUsers)
BEGIN
	SELECT TOP 1
	  @FirstName = FirstName,
	  @LastName = LastName,
	  @EMailAddress = @EMailAddress,
	  @Login = Username,
	  @MobileNumber = MobileNumber
		FROM ##ADUsers

	EXEC sstp_Security_Users_Create
		@Login = @Login,
		@FirstName = @FirstName,
		@LastName = @LastName,
		@UserEMailAddress = @EMailAddress,
		@UserSMS = @MobileNumber

	EXEC sstp_Security_Users_CopyGroupsMemberships
		@FromUser = @TemplateUser,
		@ToUser = @Login

	DELETE
		FROM ##ADUsers
		WHERE Username = @Login
END

DROP TABLE ##ADUsers

This script will create the logins that doesn’t already exist in the system and copy AppFrameTemplateUser’s permissions to the logins. Running this script every night or similar will make it much easier for our customers to add users to AppFrame (or PIMS).

NB! Please notice that the @OrgUnit is “backwards”, meaning that the top level OU is on the right. Office1->Department1 is therefore OU=Department1,OU=Office1

Christmas? Not yet

I’m now back in Ølen, or as I like to see it; back to reality. I could have been at TechEd for ages, “but but, it’s not only only” as Leif like to say. It’s direct translated from Norwegian. You English-speaking-dudes probably see the humour with that 😛 Anyhow, now we’ve started with TechEd “sessions” in Ølen. Pretty interesting to gather Omega-nerds together (with pizza) and watch Mr. Bob speak like he’s done a thousand times before.

Next Pizza & Learning in Ølen will be about SQL Replication, so I’ve just started to prepare for it. I’ll make an input here when I’m done. SQL Replication is something I think our customers will ask for. In the past we’ve talked them out of it, because it was too much hassle, but from what I understand of both Microsoft and users it’s now MUCH easier. This will also help me on my way to take a SQL 2005 exam. I’ve taken one for 2000, but that’s old shit now. When I’ve passed 2005 (hopefully on first attempt), I’ll bet my ass it doesn’t take more than two months before 2008 exams is out.

The last week I’ve worked a lot with the technology servers. We’ve split up Omegas server-park. Now JanCB (and the rest of IT/Drift) is responsible for Omega’s production servers (teamdoc, mail etc), and I’m responsible for all test and development servers. It’s fun to vary your day with various things. CR1, CR2, web-support (even though I’ve never developed ANY web-stuff for Omega), server-issues etc.

This Sunday we installed a new server-rack for the test-server-room. SEXY! I need one myself at home! And if I ever get a wife, I’ll teach her how to clean it 🙂 I know what you’re thinking. Poor wife! As Stephen Forte (one of the speakers at TechEd) said, his wife took too much time, so instead he’s got a laptop with SQL Server installed in his bed. Sounds familiar…

I’m sorry that we can’t share the TechEd sessions with you. The streams are DRM-protected, and you need username and password to see them. I’m sure you understand that we don’t want to give our username and password to everyone. Microsoft might throw us out if we’re too many logged in. When I’m in Stavanger next time if anyone is interested, I’ll put it on the big screen. I’m always open for a session! Also, when (if) I’m going to Lithuania soon, I’ll put it on the big screen there too!

I do recommend you setting up Pizza & Learning where it’s possible. It’s very interesting. I know it’s not just me who think that! If you need help with subjects, you should stop thinking about pizza and start thinking about work 🙂 In Ølen we’ve had the following so far:
New features in SQL 2005, AppFrame Grid Control, Infrastructure in Omega, SQL Tuning (2 sessions), Active Directory and Exchange and this time we’ll have SQL Replication. I’m going to vote for PIMS-modules next time! If you need info about one (or more) of these, do not hesitate to contact me!

PS!!! It really annoys me that it’s not possible to have subjects inside subjects in TeamDoc. Hope this will be fixed in the near future.