Lync Server 2010 – Deleting User Contacts Via SQL

I recently experienced an issue at a customer whereby they had used the very helpful LyncAddContacts VBS script from the EXPTA blog, however this process had gone slightly wrong for the customer and they wanted to delete all contacts that had been pushed out to users and start again. Unfortunately the handy dbimpexp.exe tool does not allow you to explicitly delete contacts from users in bulk or individually. To get around this issue I utilised the following Microsoft SQL query against the Lync RTC database in order to delete a users contacts in their entirety. Please use the following information with caution, the query listed below modifies tables in the RTC database and should be used at your own discretion.

1. Using the Microsoft SQL Management Studio tools connect LyncServerName\RTC using an account that has full CSAdministrator rights.

2. When connected, under databases right click “RTC” and select “New Query”.

3. In the new query entry fieldtype the following:

DECLARE @RC int
DECLARE @_Owner nvarchar(4000)
EXECUTE @RC = [rtc].[dbo].[ImpDeleteContactGroups2] “user@sipdomain.co.uk
GO

4. Under the Execute command detailed above change the users SIP address to be the desired one. To run this for multiple people at once, add more Execute lines for each person and then click Execute in the tool bar to run the script
 
5. Once the query has run, log into the Lync 2010 client as the user(s) and ensure their contacts list is now blank.
 
That’s it, the process is complete.

Lync Server 2010 – ABSConfig Issue

I was recently using the ABSConfig.exe tool, which is a part of the Lync Server 2010 Resource Kit, to make some modifications to the address book for number normalisation. After playing around for a while I decided, to be safe, I would use the “Restore Defaults” button on the ABSConfig tool to put me back to where I started. Soon after clicking the button it appeared all was not well. I noticed that when querying users in the Lync 2010 client, that opposed to it showing a users given name, it was actually showing their job title. I also noticed that corporate photo graphs for new users, ones that were not in my clients ABS cache file, were not being displayed. It tuns out that the ABSConfig.exe tool in the Lync Resource Kit had been released by Microsoft still containing the OCS 2007 R2 database modification. Effectively, clicking Restore Defaults in the ABSConfig tool in a Lync front end server caused the RTC database to be overwritten with OCS attributes resulting in a pretty much destroyed address book.

I located a great blog post here, which provides an SQL query to restore the address book attributes in the RTC database back to Lync RTM. This worked great for name lookups but my corporate photographs would still not sync. After a weekend of investigating the issue, I decided to contact Microsoft Partner Support, who provided me with a SQL query that resolved all my address book and corporate photograph issues. The solution provided is detailed below:

1. On the standard edition server, or the server which holds the RTC database, install the Microsoft  SQL Server 2008 Management Studio and connect to the RTC database.

2. Click on “New Query” and paste the following SQL into the query window and click the execute button. The execute button is the small green “play” icon.

use rtc
exec dbo.RtcDeleteAbAttributes
exec dbo.RtcAddAbAttribute 1, N’givenName’, 0x01400000
exec dbo.RtcAddAbAttribute 2, N’sn’, 0x02400000
exec dbo.RtcAddAbAttribute 3, N’displayName’, 0x03420000
exec dbo.RtcAddAbAttribute 4, N’title’, 0x04000000
exec dbo.RtcAddAbAttribute 5, N’mailNickname’, 0x05400000
exec dbo.RtcAddAbAttribute 6, N’company’, 0x06000000
exec dbo.RtcAddAbAttribute 7, N’physicalDeliveryOfficeName’, 0x07000000
exec dbo.RtcAddAbAttribute 8, N’msRTCSIP-PrimaryUserAddress’, 0x08520C00
exec dbo.RtcAddAbAttribute 9, N’telephoneNumber’, 0x09622800
exec dbo.RtcAddAbAttribute 10, N’homePhone’, 0x0A302800
exec dbo.RtcAddAbAttribute 11, N’mobile’, 0x0B622800
exec dbo.RtcAddAbAttribute 12, N’otherTelephone’, 0x0C302000
exec dbo.RtcAddAbAttribute 13, N’ipPhone’, 0x0D302000
exec dbo.RtcAddAbAttribute 14, N’mail’, 0x0E500000
exec dbo.RtcAddAbAttribute 15, N’groupType’, 0x0F010800
exec dbo.RtcAddAbAttribute 16, N’Department’, 0x10000000
exec dbo.RtcAddAbAttribute 17, N’Description’, 0x11000100
exec dbo.RtcAddAbAttribute 18, N’manager’, 0x12040001
exec dbo.RtcAddAbAttribute 19, N’proxyAddresses’, 0x00500105
exec dbo.RtcAddAbAttribute 20, N’msExchHideFromAddressLists’, 0xFF000003

3. From the Lync Management Shell run the following command: Update-CsUserDatabase

4. Open up the servers event viewer and wait until you can see Lync events 30024, 30027 & 30028 before proceeding with the next step.

5. From the Lync Management Shell run the following command: Update-CsAddressBook and wait around ten minutes before proceeding with the next step.

6. Open a Lync 2010 client and wait for the address book to download, this should have now resolved all address book issues.

Thanks to Mike Halfacree at Microsoft UC Support for the resolution.