Thursday, October 22, 2009

Turn 3 dimensional (pivot) table to junction table

Scenario: You have a "three dimensional table" in Excel where the rows are articles and the columns are suppliers, and each cell in the table are marked with an x (or number indicating something) indicated which articles and suppliers that are related. You have decided to create a database instead with one table with articles and another with suppliers. Let's say one article can have multiple suppliers and one supplier can deliver multiple articles, i.e. you need an junction table. How do you create a junction table from this three dimensional table?

My Solution: Search thorugh each row in the table fro a value larger than zero (anything but zero or empty) , for each hit write a line in a textfile with the supplier, article and cell value. Then use the text file to create your junction table.

The code
Sub ConvertTable()
Dim str, strForb As String
Dim col As Integer

Sheets("Sheet1").Select

Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("Rows.txt")

For i = 2 To 5 'enter which row that contains data
strArticle = Range("A" & i).Value
For Each Cell In Range("B" & i & ":E" & i)
If Cell.Value > 0 Then
col = Cell.Column
str = strForb & "," & Range(ColNo2ColRef(col) & 1) & "," & Cell.Value
objFile.WriteLine (str & cbrlf)
End If
Next Cell
Next i
objFile.Close

End Sub

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo <> 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function

Tuesday, October 13, 2009

Macro to paste unformatted text in word

This macro gives you the possibility to easily paste text into a word document without formatting.

  • Copy a bit of text
  • Choose the Developer tab
  • Record macro (save in the norma.dotm template)
  • Choose a smart keyboard combination, for example ctrl + shift + v
  • Paste the text somewhere in the document
  • Stop the macro
  • Edit the macro and replace the one line with
  • Selection.PasteSpecial Link:=False, DataType:=wdPasteText

Monday, October 12, 2009

Create domain trust

Scenario: You want to be able to use your domainA account to log in to domain B. I.e. you wan't domain B to trust domain A.

First thing, open firewall ports (both directions on all fw between the domains)
Kerberos 88 TCP & UDP
LDAP 389 TCP & UDP
LDAP GC 3268 TCP
LDAP GC SSL 3269 TCP
LDAP SSL 636 TCP
RPC 135 TCP
SMB 445 TCP & UDP
NetBIOS 137-139 TCP & UDP
WINS 1512 TCP & UDP
WINS Replication 42 TCP
 
make sure the DNS in the domain controllers for both domains are properly setup. The own domain shall be setup as a primary zone while the other shall be a secondary zone. and vice versa. Same thing for reverse lookup zones. Also make sure that the DNS zone for domain B has a folder named _msdcs. If not, restart netlogon, and run ipconfig /flushdns and ipconfig /registerdns.
 
Make sure the clocks are in sync, preferably syncronized to the same external time server.
Make sure RPC is working by entering "\\DC1.domainA.com" in explorer on a computer in domainB and vice versa.

Create trust
On a DC in domainA.
Administrative tools --> Active Directory Domains and Trust --> Right click domainA.com, choose Properties --> Tab Trusts --> Create new Trust --> enter DomainB.com -->
--> "External Trust" --> "One way incoming" --> "Both this domain and the specified domain" --> enter credentials for an account with administrator privileges in domainB --> "Domain-wide authentication" --> "Trust creation complete".
Validate Trust
Log on to a DC in domainB
Administrative tools --> Active Directory Domains and Trusts --> Right click DomainB.com and choose Properties --> tab Trusts --> mark domainA --> Validate.
Permissions
Permissions are not inherited between domains. This are solved by adding a domainA account to a domainB secureity group (for example the built in group "Administrators"). If you want to add a domainA account to a custom group in domainB, the group must be a security group of type "local", not "global".

Local admins
Even if you add a domainA account to the Administrators group in domainB, the account will not be local administrator on the computers in domainB. The only solution i now off is to manaulle make the DomainA account to local administrator on each server/host using a domainB or local account.

Wednesday, October 7, 2009

Server 2003, 2008 log references

http://www.windowsecurity.com/articles/Event-IDs-Windows-Server-2008-Vista-Revealed.html

Useful tools for Active Directory

netdiag

dcdiag

nltest

nslookup

Tool for restoring deleted files

Recuva

NIST firewall policy

Google sp800-41.pdf

Enable anonymous access in Sharepoint.

Enabling anonymous access to a sharepoint site requires to separate activities.

1. Enable in Central Administration: Application management --> authentication providers --> Default --> Enable anonymous access

Note: DO NOT CHANGE ANYTHING IN IIS

Navigate to the site where you want to enable anonymous access: Site actions --> Site settings --> Advanced permissions --> Settings --> Here you should see an option for Anonymous access.

Cool tool for recording streamed multimedia

http://sdp.ppona.com/

Translate business security needs to the configuration and management of security devices.

Reliable process for security policy development

“We focus in this paper on the problem of configuring and managing network security devices, such as Firewalls, Virtual Private Network (VPN) tunnels, and Intrusion Detection Systems (IDSs). Our proposal is the following. First, we formally specify the security requirements of a given system by using an expressive access control model. As a result, we obtain an abstract security policy, which is free of ambiguities, redundancies or unnecessary details. Second, we deploy such an abstract policy through a set of automatic compilations into the security devices of the system. This proposed deployment process not only simplifies the security administrator's job, but also guarantees a resulting configuration free of anomalies and/or inconsistencies.”

Schedule backup with synctoy

http://www.mydigitallife.info/2008/01/01/schedule-synctoy-to-run-and-automatically-and-repetitively/

Schedule SyncToy Sync Task in Windows Vista

  1. Click on the Start menu, then select All Programs - Accessories - System Tools - Task Scheduler.
  2. Click on Create Basic Task in the “Actions” pane on the right.
  3. In the “Create Basic Task Wizard”, type in a Name and Description. For example, SyncToy Synchronization.
  4. Click on Next button.
  5. Choose the frequency of when do you want the task to start. If you always make changes to the files, Daily is a good choice.
  6. Click on Next button.
  7. Choose date and times (if applicable) to trigger and run the task.
  8. Click on Next button.
  9. In the “Action” step, select Start a Program as the option for the task to perform.
  10. Click on Next button.
  11. Under “Program/Script”, click on Browse button and locate the SyncToyCmd.exe. Normally it’s located in “C:\Program Files\SyncToy 2.0 Beta\SyncToyCmd.exe” for SyncToy 2.0 Beta.
  12. In the “Add Arguments” textbox, type -R. –R will run all folder pairs that are active for run all. If you want to run just a single folder pair, add –R”folder pair name” to the end of the command line.

    Note: there is a space before the hyphen but not one after the R. If the folder pair name contains a space, surround it with quotation marks, as the example above shows. For another example, assume that SyncToy is in the folder named C:\Program Files\SyncToy 2.0\ and that you want to run a folder pair named “My folder pair.” Enter the command line as follows, including the quotation marks: “C:\Program Files\SyncToy 2.0\SyncToyCmd.exe” -R”My folder pair.” Note that there are two sets of quotation marks in this case: one is around the path to the SyncToy program file and the other surrounds the folder pair name.

Schedule a SyncToy Task in Windows XP

  1. Go to the Start menu, select All Programs - Accessories - System Tools - Scheduled Tasks.
  2. Select Add scheduled task to start the Scheduled Task Wizard. You will see a list of possible programs to run.
  3. SyncToy may appear as an option in the list. If SyncToy does not appear in the list, click Browse and locate the SyncToy executable. Normally it’s installed in Program Files directory.
  4. The wizard will next prompt you to enter how often you want to run the scheduled SyncToy (for example, daily, weekly, et cetera). Select a frequency.
  5. The next page asks when to start the task. Select a start time.
  6. The next page asks for the user name and password to run the program under. Enter your user name and password. However, in SynToy 2.0, it’s not necessary for the defined user to log in to the computer. Different user or no one logged on is possible to have the task running too.
  7. The final page contains an option to open the properties dialog when the wizard ends. Select this checkbox.
  8. Modify the Run textbox to include the –R command line option. –R all by itself will run all folder pairs that are active for run all. If you want to run just a single folder pair, add –R“My Pair” to the end of the command line.

    Note: there is a space before the hyphen but not one after the R. If the folder pair name contains a space, surround it with quotation marks, as the example above shows. For another example, assume that SyncToy is in the folder named C:\My Folder and that you want to run a folder pair named “My folder pair.” Enter the command line as follows, including the quotation marks: “C:\Program Files\SyncToy 2.0\SyncToyCmd.exe” -R”My folder pair.” Note that there are two sets of quotation marks in this case: one is around the path to the SyncToy program file and the other surrounds the folder pair name.

Install EventSentry on a 64 bit Sharepoint server

Scenario: You want to install Eventsentry on a 64 bit server hosting sharepoint. The problem is that the web reports in Eventsentry, in the case of 64 bit, require IIS to run in 32 bit mode, which is not an option an an 64 bit SP install. The approach was to put the web reports on a different server.

  1. Make sure to have an ES config export file as well as an export only including filter packages and an export file for the custom web reports.
  2. Install MySQL as outlined in the ES documentation
  3. Install ODBC driver (wait to configure)
  4. Install ES without DB support and web reports
  5. Run modify wizard and add MySQL support.
  6. From the Management interface, add an db action to test the database.
  7. Configure ODBC System DSN with the eventsentry_svc user. Test if it works
  8. Import the config XML file. Restart the GUI.
    1. Check if everything seems properly done.
      1. In particular, reconfigure the DB action if needed.
  9. Copy the Eventsentry folder under Program Files to the server used for the web reports (use the same place). Set permissions on the folder to only allow admininstrator access (and the System account).
  10. Make sure that Active Server Pages is added in Windows Componenents (Application Server --> Details, Internet Informations Service --> Details, World Wide Web Service --> Details, Check Active Server Pages
  11. On the web server, create a new web site, point it to the copied Eventsentry folder (eventsentry/web).
  12. Add “index.asp” as “document” in the properties for the web site (in IIS).
  13. Check Windows integrated authentication in IIS Manager.
  14. The first time you access the web reports, choose to disable authentication (trust the NTFS permissions).
  15. You propably need to reconfigure the database setting on the Default Profile.

Move Sharepoint to 64 bit server

Scenario: A Sharepoint stand-alone (Basic Install with SQL Express) environment needs to be moved from a 32 bit server to a 64 bit server. The orignal installation is done from a Swedish installer while the new installation is done from a english installation.

This is what worked for me:

  1. Install Win Server 2003 64 bit.  I had to use a Dell CD to manage to install on the RAID setup.
  2. Install MOSS 2007 English from CD (Install from a downloaded copy did not work, the config wzard hung)
    1. Choose basic installation
    2. Run the config wizard
    3. Download language pack (.img opens with WinRAR)
    4. Install language pack (make sure it is the right one, swedish, 64 bit, moss 2007 etc.)
    5. Run the config wizard again
  3. Run Microsoft Update. Make sure to get the latest SP updates. At least newer than the environmnet your moving from.
  4. Delete the site collection via Central Adminstration and create a new one with the right language pack. Use the same template as the site you want to move.
    1. You can check what template was used by creating a aspx site. Google “determine site template”
  5. In my case the site template used was now deprecated. My solution was to create a custom site template, upload it to the created site and create is as an subsite. However, I got “File not found”-error on the new site.
  6. At this point, i used STSADM to make a backup of the original site
  7. Restored the backup to the faulting subsite using STSADM
  8. Voila, an exact copy was now up and running on the new server!
    1. It did bring up a login dialog when browsing on the local server, but not browsing from a arbitrary client (or other server).
  9. Added a hostname “temp” and created a DNS A record “temp@domain.com” pointing to the IP adress of the new server.