.Net Convert Your Site From PlainText Passwords to Salted Hashes

Why Change your passwords to Salted Hashes

I’ve had a site that for a long time has been on my TODO list to convert the site from using plaintext passwords to using passwords that are hashed and salted. No matter what the excuse, the truth is, it’s not really just your data that you’re protecting by encrypting your passwords, but it’s the user, since many still use the same password on many systems.

Our Conversion Steps

1. Build a command line application to convert all passwords in our SQL server to hashed data with salt.

2. Change current code in the site when a user has forgotten their password.

3. Change current code in site for when a new user is created.

4. Change current code in site for when a user wants to change their password.

5. Change the code for user login to except the plaintext password they enter, hash/salt it and compare it to what’s in the DB

 

Advice & Example sites:

While doing research on hashing and what/how to do it, I’m learned a couple things. 1. Don’t write your own encryption (You are not an expert!) 2. If you can decrypt the passwords back to plaintext then someone else probably can (in our code we will never be decryption but we’ll take what the user says is their password and encrypt it and compare it to what’s in the database)  3. Do not use MD5, while one of our example sites has the option, it’s just for example and for the same reason always salt your hashes (see: https://crackstation.net/ )

The 2 sites that I used to for code samples are:

1. The how to for salted hashes How to hash data with salt in C# or VB.NET

2. How to recover and reset the forgot/lost password in asp.net using activation link in email id

 

Converting All Current Passwords

To convert our current passwords from Plaintext to salted hash passwords we’ll need to build a small command line application to loop through the SQL table, encrypt, created the hash and salt it and then write it to the sql. I created a new column (nvarchar(150)) next to my current password column (so I could test logins) Also I shouldn’t have to say but do this in a test DB.

Using the code samples from Site 1 listed above lets first create an array or list of all current users with their passwords


sub getpasswords()
 Dim strSQL As String

 Dim sourceids As String = Nothing
 Dim conn1 As New SqlConnection("Your Server Connection")
 strSQL = "SELECT * FROM tblLogIn"
 conn1.Open()

 Dim newPass As New List(Of PasswordStruct)

 Dim kmd As New SqlCommand(strSQL, conn1)
 Dim r As SqlDataReader
 r = kmd.ExecuteReader()

'loop through table and add each user/password to a list
 If r.HasRows Then
 While r.Read()
 newPass.Add(New PasswordStruct(r("UserID"), r("USerName"), r("Password")))
 End While

 End If

 Dim PasswordCollection As ICollection = newPass
 For Each s As PasswordStruct In PasswordCollection

 'starts the function ComputeHash with password, userid, type of hash algorithm (choose one) and saltbytes
 ComputeHash(s.Password, s.UserID, "SHA256", Nothing)

 Next

 End sub

using the sample code for ComputeHash (see site #1 above), adding the argument for the userid and add this code to write each new hash to the DB


Dim strSQL As String
 'Dim conn As SqlConnection
 Dim sourceids As String = Nothing
 Dim conn1 As New SqlConnection("your sql connection")
 'update since records already exist
 strSQL = "UPDATE tblLogIn SET NewPassword=@computehash where userid =@userid

 Dim objkmd As New SqlCommand(strSQL, conn1)
 objkmd.CommandType = CommandType.Text
<pre>objkmd.Parameters.Add("@computehash", Convert.ToString(Request.QueryString("uCode")))
<pre> objkmd.Parameters.Add("@userid", Convert.ToString(Request.QueryString("UserId")))
 Try
 conn1.Open()
 objkmd.ExecuteNonQuery()
 Catch ex As Exception

 Finally

 objkmd.Dispose()
 conn1.Close()
 conn1.Dispose()
 End Try

You now have a column in your table with all the current users passwords hashed and salted.Forgotten Passwords

Forgotten, New and Changing Passwords

If your site is currently using plaintext passwords, odds are that when a user forgets their password, you are sending them their password in plaintext from the DB to their email address. Now since we’ve hashed and salted the new passwords, if you sent the user the long string of code in the DB, this wouldn’t help them login to the site. Our only option is to have the user change their password. We’ll email the user a link to the new password page, allow them to add a new password, from there we’ll do the same we did above, take the plaintext password and encrypt and salt it.

For this section we’ll be using the sample code on site #1 and site #2 above. Since you probably already have modules for the changing passwords or creating passwords for new users, I will not be adding code here, but telling you an outline of what you need to do.

Forgotten Passwords

Following the lead on example code site #2 , create your forgotten password page or add a panel on your current change password screen with the email fields and the password fields laid out on the website. Since the site doesn’t dive into if you have encrypted passwords, here is where you’ll need to make a change.

Using example code site #1, you’ll want to make a class for your own site, by making this code a class, you’ll be able to reuse the code not only for forgotten passwords but also for new user passwords and users changing their passwords.

Now once the user follows the long link provided in their email to change their password, they’ll input their new password and you’ll call ComputeHash with the arguments of  the plaintext password they supplied, the hashAlgorithm and saltbytes.

Fine, lets do it

Below is the code from Example site #2 with changes for computing the hash, be sure to update the code with your table and column names.


Protected Sub btnChangePwd_Click(sender As Object, e As System.EventArgs) Handles btnChangePwd.Click
Try
Dim thepwdhash as string

thepwdhash = SimpleHash.ComputeHash(txtNewPwd.Text.Text, "SHA256", Nothing) 'added code
' Here we will update the new password and also set the unique code to null so that it can be used only for once.
cmd = New SqlCommand("update Tbl_Login set UniqueCode='',Pwd=@pwd where UniqueCode=@uniqueCode and (EmailId=@emailid or UserName=@username)", con)
cmd.Parameters.AddWithValue("@uniqueCode", Convert.ToString(Request.QueryString("uCode")))
cmd.Parameters.AddWithValue("@emailid", Convert.ToString(Request.QueryString("emailId")))
cmd.Parameters.AddWithValue("@username", Convert.ToString(Request.QueryString("uName")))
cmd.Parameters.AddWithValue("@pwd", thepwdhash) ' changed pwd is the new hash instead of the plaintext that the user supplied
If con.State = ConnectionState.Closed Then
con.Open()
End If
cmd.ExecuteNonQuery()
lblStatus.Text = "Your password has been updated successfully."
txtNewPwd.Text = String.Empty
txtConfirmPwd.Text = String.Empty
Catch ex As Exception
lblStatus.Text = "Error Occured : " & ex.Message.ToString()
Finally
cmd.Dispose()
con.Close()
End Try
End Sub

The same can be done with your module for New User Password because there isn’t a password and you’re writing a new one, like above where we don’t care what the password was, we’re just writing a new one. It changes a bit for Changing Passwords.

Changing Passwords

When changing passwords, the difference is, we want to check the “old” password before we change the new password. You’ve probably already have code with textboxes that have one box for old password, 2 boxes for new password and a submit button.

Once the submit button is pressed we need to check the old password first. IF the old password matches, we can then once again compute the hash and write it over the old password


Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSubmit.Click
If Page.IsValid Then
'check old password
Dim OldPassTrue As String
OldPassTrue = CheckOldPassword(PSW.Text)
If OldPassTrue = "True" Then

ComputeHash(PSW1.Text, lblEmail.Text, "SHA256", Nothing)
'then write the new password to the password column
Else
lblUserMessages.Text = "<br>Your old password does not match."
lblUserMessages.Visible = "true"
End If
End If
End Sub

Function CheckOldPassword(ByVal OldPass As String) As Boolean

Dim strSQL As String
Dim objConn As SqlConnection
Dim objkmd As SqlCommand
Dim OldPassGood As String = Nothing

strSQL = "SELECT NewPassword FROM tblLogin"
strSQL += " WHERE UserName = '" & username & "'"

objConn = New SqlConnection(ConfigurationManager.AppSettings("conn"))

objkmd = New SqlCommand(strSQL, objConn)
objkmd.CommandType = CommandType.Text
Try
objkmd.Connection.Open()
Dim dr As SqlDataReader = objkmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows() Then
dr.Read()

Dim passwordHashSha256 As String = dr("NewPassword")
OldPassGood = VerifyHash(OldPass, "SHA256", passwordHashSha256).ToString()

End If
dr.Close()

Catch ex As Exception
Response.Write(ex.ToString())
Finally
objkmd.Connection.Close()
objkmd.Dispose()
End Try
return OldPassGood
End Function

 

User Login

Last on our list of TODOs, users need to be able to login. If you’re currently using plaintext passwords, you’re just checking against the DB is the username and password match. We now need to (again) compute the hash for what the say is the password and then compare it against what’s in the DB, if the hashes match they the user is allowed in the site. You notice that we never decrypt the hash but just compare.

I added to my existing login code, after the user hit submit to login to the site


Dim DoesPasswordPass As Boolean

DoesPasswordPass = checkpassword(pass, user) 'if DoesPasswordPass = "True" then the user is allowed in the site

 


Public Shared Function checkpassword(ByVal password As String, ByVal user As String)

 Dim passwordHashSha256 As String
 Dim strSQL As String
 Dim PasswordPass As Boolean
 Dim sourceids As String = Nothing
 Dim conn1 As New SqlConnection(ConfigurationManager.AppSettings("readconn")) 'readonly

 strSQL = "SELECT NewPassword FROM tbllogin where userName= @userName"

 conn1.Open()

 Dim kmd As New SqlCommand(strSQL, conn1)

 kmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = user
 passwordHashSha256 = kmd.ExecuteScalar().ToString()

 conn1.Dispose()
 kmd.Dispose()

 PasswordPass = VerifyHash(password, user, "SHA256", passwordHashSha256).ToString()</pre>
'password is what the user thinks is their password, SHA256 is hash type,  Return PasswordPass is
what the current hash is

 Return PasswordPass

 End Function

 

 

 

 

 

 

Technorati Tags: , , , , , ,

     

Can’t use TextBoxWatermarkExtender on Password Textbox in ASP.NET

On my new site I wanted to have the “fancy” text in the textbox that would clear once someone clicks in the box to start typing.

textboxwatermark

In ASP.NET there’s a nice AJAX extender called TextBoxWatermarkExtender that allows for this to happen, except if you set your textbox to type “password”, what ever work you add to the watermark shows as ********.

Thanks to this link: Display WaterMark text for Password TextBox in ASP.Net. I had my username and password up and showing in no time.

     

System.Web.Mail The classes in this namespace have been deprecated

Switching from system.web.mail to System.Net.Mail is pretty easy and straight forward, the syntax is a little different and instead of having the server declared in the class you declare it in the web.config.

Old System.Web.Mail version

Function SendEmail(ByVal strEmailTo As String, ByVal strEmailFrom As String, ByVal strSubject As String, ByVal strBody As String, ByVal strBodyFormat As String) As Boolean
        Dim email As New System.Web.Mail.MailMessage
        Dim bSent As Boolean = False
        If Trim(strEmailTo) <> "" Then
            email.To = strEmailTo
            email.From = strEmailFrom
            email.Body = strBody
            email.Subject = strSubject
            If UCase(strBodyFormat) = "TEXT" Then
                email.BodyFormat = Web.Mail.MailFormat.Text
            Else
                email.BodyFormat = Web.Mail.MailFormat.Html
            End If
            System.Web.Mail.SmtpMail.SmtpServer = "smtp.yourdomain.com"
			Try
				System.Web.Mail.SmtpMail.Send(email)
				bSent = True
			Catch ex As Exception
				bSent = False
			End Try
		End If
		SendEmail = bSent
    End Function

New System.net.Mail Version

 Function SendEmail(ByVal strEmailTo As String, ByVal strEmailFrom As String, ByVal strSubject As String, ByVal strBody As String, ByVal strBodyFormat As String) As Boolean
        Dim email As New MailMessage()
        Dim bSent As Boolean = False
        If Trim(strEmailTo) <> "" Then
            email.To.Add(New MailAddress(strEmailTo))
            email.From = New MailAddress(strEmailFrom)
            email.Body = strBody
            email.Subject = strSubject
            If UCase(strBodyFormat) = "TEXT" Then
                email.IsBodyHtml = "false"
            Else
                email.IsBodyHtml = "true"
            End If
            
            Try
                'System.Web.Mail.SmtpMail.Send(email)
                Dim mSmtpClient As New SmtpClient()
                ' Send the mail message
                mSmtpClient.Send(email)
                bSent = True
            Catch ex As Exception
                bSent = False
            End Try
        End If
        SendEmail = bSent
    End Function

Add the server to the web.config

<configuration>
   <system.net>
      <mailSettings>
         <smtp from="defaultEmail@yourdomain.com">
            <network host="smtp.yourdomain.com" port="25" userName="yourUserName" password="yourPassword"/>
         </smtp>
      </mailSettings>
   </system.net>
</configuration>

source

     

Creating an Unordered List for a MultiLevel Menu in Code Behind vb.net

As mentioned in New Series: Completely Redoing the Company Website, I’m creating our members website from scratch. One of the items on my to do list is the menu, since I’ll be using master pages, the menu will be placed on every page and will be a big part of the website.

Since our website is a members site, we have different security levels, depending on what your security level is, this will determin what links you get in the menus. (example: admins get links to admin pages). So our menu needed to be created on the fly depending on what the person logged in has as a security level.

Looking at the many ways to create a menu, the one’s I like are purly CSS based. With the CSS based menus you use an unordered (bulleted) list and then use CSS to create the menu as seen at Create a Multilevel Dropdown Menu with CSS

The Code Behind

Being that we already have a website, we already have the menu structure in our SQL database.
While your SQL structure will be different, you can get a hint on how to pull each sub to create your UL. (tables and column names changed to protect the innocent)

Grabbing the Main Menu Level


Sub GetMenuMain()

        
        SecurityLevel = getUserLevel.ReturnLevel
        Dim extraline As String
        Dim menuText As String
        Dim SubMenuList As New ArrayList()
        Dim ds As New DataSet()
        Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("yoursqlconn"))
        Dim cmd As New SqlCommand("SELECT * FROM tblMainMenu where SecLvl <=" & SecurityLevel & " order by Seq", cn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        Dim table As DataTable = ds.Tables(0)
        


        Dim sb = New StringBuilder()
        If table.Rows.Count > 0 Then
            ' build the unordered list
            sb.AppendLine("<ul id='nav'>")
            For Each dr As DataRow In table.Rows
				' If this Menu Item has a sub menu
                If dr("SubMenuIDs").ToString() <> "*NONE" Then
                    SubMenuList.Add(dr("SubMenuIDs").ToString())
                    dr("SubMenuIDs").ToString() & "' data-dir='h' data-offsets='-10,5'>")
                    sb.AppendLine("<li class='mainmenu'><a href='" & dr("Pageurl").ToString() & "'>" & dr("itemName").ToString() & "</a>")
                    SubMenu = SubMenu & sb.ToString()
                    sb = New StringBuilder()
					' Go get the submenu
                    GetMenuSub(dr("SubMenuIDs").ToString())
                Else
					sb.AppendLine("<li class='mainmenu'><a href='" & dr("Pageurl").ToString() & "'>" & dr("itemName").ToString() & "</a></li>")

                End If
    
            Next
        End If
        sb.AppendLine("</ul>")
        
        SubMenu = SubMenu & sb.ToString()
        

    End Sub

Grabbing the Sub Menu Level

Sub GetMenuSub(ByVal submenuid As String)

        
        Dim ds As New DataSet()
        Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("yoursqlconn"))
        Dim cmd As New SqlCommand("SELECT * FROM tblMenusSub where SubMenuIDs = '" & submenuid & "' AND SecLvl <=" & SecurityLevel & "", cn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        Dim table As DataTable = ds.Tables(0)
		Dim sb = New StringBuilder()
        If table.Rows.Count > 0 Then
			' build sub unordered list
            sb.AppendLine("<ul>")
            For Each dr As DataRow In table.Rows
                

                Dim TestPos As Integer
				'if submenu item starts with > then there is a subsub menu
                TestPos = InStr(1, dr("SubMenuText").ToString(), ">")
                If TestPos > 0 Then
                    Dim MyChar() As Char = {">"}
                    Dim NewString As String = dr("SubMenuText").ToString().TrimStart(MyChar)
                    'get subsubmenu loop here
                    sb.AppendLine("<li><a href='" & Trim(dr("HyperLink").ToString()) & "'>" & NewString & "&nbsp;&nbsp;&nbsp;<img src='/images/arrow.gif' border='0'></a>")
                    SubMenu = SubMenu & sb.ToString()
                    sb = New StringBuilder()
					'get subsub menu
                    GetSubSubMenus(Trim(dr("HyperLink").ToString()))

                Else
                    sb.AppendLine("<li><a href='" & Trim(dr("HyperLink").ToString()) & "'>" & dr("SubMenuText").ToString() & "</a></li>")

                End If
                
            Next
            sb.AppendLine("</ul></li>")
        End If
        SubMenu = SubMenu & sb.ToString()
        

    End Sub

Grabbing the SubSub Menu Level


Sub GetSubSubMenus(ByVal hyperlink As String)
        Dim ds As New DataSet()
        Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("yoursqlconn"))
        Dim cmd As New SqlCommand("SELECT * FROM tblMenusSub where SubMenuIDs = '" & hyperlink & "' AND SecLvl <=" & SecurityLevel & " ORDER BY SubMenuID", cn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        Dim table As DataTable = ds.Tables(0)

        Dim sb = New StringBuilder()
        If table.Rows.Count > 0 Then
            sb.AppendLine("<ul>")
            For Each dr As DataRow In table.Rows
                
                sb.AppendLine("<li><a href='" & Trim(dr("HyperLink").ToString()) & "'>" & dr("SubMenuText").ToString() & "</a></li>")

            Next
            sb.AppendLine("</ul></li>")
        End If
        SubMenu = SubMenu & sb.ToString()
    End Sub

Your List

<ul id="nav">
    <li><a href="#">1 HTML</a></li>
    <li><a href="#">2 CSS</a></li>
    <li><a href="#">3 Javascript</a>
        <ul>
            <li><a href="#">3.1 jQuery</a>
                <ul>
                    <li><a href="#">3.1.1 Download</a></li>
                    <li><a href="#">3.1.2 Tutorial</a></li>
                </ul>
            </li>
            <li><a href="#">3.2 Mootools</a></li>
            <li><a href="#">3.3 Prototype</a></li>
        </ul>
    </li>
</ul>

Now use Kriesi’s page and format the CSS for your completed menu.

     

New Series: Completely Redoing the Company Website

One of my many jobs at work and the one that I was hired for, is developing the company website. Usually I’m creating new web applications that pull info or add info to the SQL server, make it fit and set the look for our current website and then test it. This is all done in ASP.NET.

It been a couple years since our site has been updated, with it being a members only site, it wasn’t a huge deal that it was a bit dated but the time has come, it’s time to create a new site and I’m excited about this.

I’ll start adding blog post about the stages I’m at and the problems and or things i’ve over come in creating this website.

     

Adding Javascript to Cvent Event Pages

Today our registration team were trying to add a jquery slider at the top of a cvent event summary page. Every time we’d add the script tags to the html editor, we’d save it and the script tags would be stripped out.

After hours of testing we called Cvent support team and here is your answer for anyone else trying this.

This is the ONLY way you can add a script to Cvent event pages.

1.  Any scripting can only go into the Event Summary Instructions section. Even if the code that uses the script is in a different section, you still have to add the script tags in this section (kind of like the <head> section in a regular page)

cvent_Event_editor

 

 

2. You can NOT add the script in the html editor, if you do it will be stripped out! What you have to do is do all your editting in the html editor without the scripting and then save it, then in the small box pictured above you add your script tags. If you ever go back into the html editor, it will also strip out the scripts so always save your code before going into the editor.

 

 

Technorati Tags: , , ,

     

Cvent API: Event Fields and Their Format

When working with Events in the API, many of the fields are searchable but when searching you don’t always know what the format of that field is. Here is an example to help those that are just getting into searching through the event objects.

FieldName,     Value,   Type

ArchiveDate #4/30/2014# Date
archiveDateField #4/30/2014# Date
archiveDateFieldSpecified True Boolean
ArchiveDateSpecified True Boolean
Capacity -1 Integer
capacityField -1 Integer
capacityFieldSpecified True Boolean
CapacitySpecified True Boolean
Category "Meeting" String
categoryField "Meeting" String
City "Las Vegas" String
cityField "Las Vegas" String
ClosedBy "" String
closedByField "" String
Country "USA" String
CountryCode "US" String
countryCodeField "US" String
countryField "USA" String
Currency "U.S. Dollar" String
currencyField "U.S. Dollar" String
EventCode "MYNB4V3XL" String
eventCodeField "MYNB4V3XL" String
EventDescription "" String
eventDescriptionField "" String
EventEndDate #1/26/2014 5:00:00 PM# Date
eventEndDateField #1/26/2014 5:00:00 PM# Date
eventEndDateFieldSpecified True Boolean
EventEndDateSpecified True Boolean
EventLaunchDate #11/1/2013 10:08:07 AM# Date
eventLaunchDateField #11/1/2013 10:08:07 AM# Date
eventLaunchDateFieldSpecified True Boolean
EventLaunchDateSpecified True Boolean
EventStartDate #1/26/2014 8:30:00 AM# Date
eventStartDateField #1/26/2014 8:30:00 AM# Date
eventStartDateFieldSpecified True Boolean
EventStartDateSpecified True Boolean
EventStatus "Active" String
eventStatusField "Active" String
EventTitle "Fly-In: National Standards" String
eventTitleField "Fly-In: National Standards" String
ExternalAuthentication False Boolean
externalAuthenticationField False Boolean
externalAuthenticationFieldSpecified True Boolean
ExternalAuthenticationSpecified True Boolean
Hidden False Boolean
hiddenField False Boolean
Id "45DA1D13-92B1-4871-80FF-3AD7DE724FEE" String
idField "45DA1D13-92B1-4871-80FF-3AD7DE724FEE" String
InternalNote "" String
internalNoteField "" String
LastModifiedDate #11/29/2013 12:14:18 PM# Date
lastModifiedDateField #11/29/2013 12:14:18 PM# Date
lastModifiedDateFieldSpecified True Boolean
LastModifiedDateSpecified True Boolean
Location "The Cosmopolitan of Las Vegas" String
locationField "The Cosmopolitan of Las Vegas" String
MeetingRequestId "00000000-0000-0000-0000-000000000000" String
meetingRequestIdField "00000000-0000-0000-0000-000000000000" String
MessageId Nothing String
messageIdField Nothing String
PhoneNumber "1-000-000-0000" String
phoneNumberField "1-000-000-0000" String
PlannerEmailAddress "registrar@domain.org" String
plannerEmailAddressField "registrar@domain.org" String
PlannerFirstName "Bill" String
plannerFirstNameField "Bill" String
PlannerLastName "Registrar" String
plannerLastNameField "Registrar" String
PlanningStatus "" String
planningStatusField "" String
PostalCode "89109" String
postalCodeField "89109" String
RSVPbyDate #1/20/2014 11:59:00 PM# Date
rSVPbyDateField #1/20/2014 11:59:00 PM# Date
rSVPbyDateFieldSpecified True Boolean
RSVPbyDateSpecified True Boolean
State "Nevada" String
StateCode "NV" String
stateCodeField "NV" String
stateField "Nevada" String
StreetAddress1 "3708 Las Vegas Boulevard South" String
streetAddress1Field "3708 Las Vegas Boulevard South" String
StreetAddress2 "" String
streetAddress2Field "" String
StreetAddress3 "" String
streetAddress3Field "" String
Timezone "Pacific Time" String
timezoneField "Pacific Time" String

Not shown but remember when searching through an Event Custom Field, the structure is

thesearch.Filter(0).Field = “CustomEventField:3AE90367-1300-4BDD-A2D4-81030987F581″
thesearch.Filter(0).Value = “Conference Call”

The API docs show CustomEventField<FieldID> but in cvent (website) if you look at your custom fields the “fieldID” is shown as

Field Stub: 3ae90367-1300-4Bdd-a2d4-81030987f581

 

 

     

Cvent API: Compare Groups with your SQL Database

It’s been a couple years since I created the program to add and delete people from cvent standard groups as they’re added or deleted from our SQL database. Over time some of the groups have gotten out of sync because there will always be times with network outages, cvent is down or the nightly script just goes bad. With 10K users and over 100+ groups these is no way to update att groups all the time because of API limitations through Cvent, So I needed a way to compare the groups. The program I worte can be run every once in a while (maybe every 6 months) and get all groups back in sync.

Again because of the amount of members/groups we have, this needs to be done on a per group basis.

I first created a drop down list of all our Cvent Standard groups, I pulled these from our SQL table that I have our internal group name, cvents group name and cvents group ID. This helped because it doesn’t look as if in Cvent API you can choose between standard and distrubution lists.

Once someone choose the group they wanted to compare, the magic would begin.

First I pull a list of users from our SQL and put them in a list(of T)


Sub GetMembersFromSQL(ByVal groupAbbr As String)

Dim SQLlist As New List(Of Integer)

Using connection As New SqlConnection("Server=YOUR SERVER CONNECTION")

Dim sql As String = "SELECT YOUR MEMBERIDS FROM YOUR GROUP"
 connection.Open()
 Dim command As New SqlCommand(sql, connection)

Using reader As SqlDataReader = command.ExecuteReader()
 While reader.Read()
 SQLlist.Add(reader.GetInt32(reader.GetOrdinal("IndividualID")))
 End While
 End Using
 End Using

GetGroup(ddlGroupNames.SelectedValue)

End Sub

Now we get info from Cvent:

login into to Cvent API (see.. http://tek-works.com/knee-deep-in-cvent-api-logging-in/)

Grab all the id’s of users in the choosen group: (remember Cvent groupID pulled from database and added to the drop down list)


Sub GetGroup(ByVal groupID As String)
 Dim ids As String()
 Dim thesearch As New com.cvent.api.CvSearch()

 thesearch.Filter = New com.cvent.api.Filter(0) {}
 thesearch.Filter(0) = New com.cvent.api.Filter()

thesearch.Filter(0).Field = "GroupId"
 thesearch.Filter(0).Value = groupID

thesearch.Filter(0).Operator = com.cvent.api.CvSearchOperatorType.Equals

ids = _ws.Search(com.cvent.api.CvObjectType.[Contact], thesearch)
 GetCventSourceIDs(ids)
 End Sub

In Cvent we have all users have a sourceid (or at least they should, I also cover later if they don’t have one) but I need that source id to match with our SQL memberid’s

Here we run into another of the API limits, Cvent will only allow you to retrieve up to 200 objects at a time, so if the group has over 200 members, we need to do the retrevials in batches.

This code will retrieve all the id’s but if the list is over 190 (I give a little breathing room) I split the list, you can also see that if the user doesn’t have a sourceID, I put them in a different list, so we can correct it.


Sub GetCventSourceIDs(ByVal ids As String())

Dim objects As com.cvent.api.CvObject()
 Dim i As Int16
Dim Cventlist As New List(Of Integer)
 If ids.Length > 190 Then

 SplitIds(ids)
 Else
 If Not IsDBNull(ids) Then

objects = _ws.Retrieve(com.cvent.api.CvObjectType.contacts, ids) 'drop the s in contacts, it was messing up the blog
 For i = 0 To objects.Length - 1

Dim source As com.cvent.api.Contacts = objects(i) 'drop the s in contacts, it was messing up the blog
 If source.SourceId = "" Then
 NoSourcelist.Add(source.FirstName & " " & source.LastName & "(in Cvent)")
 Else
 Cventlist.Add(source.SourceId)
 End If

 Next i
 End If

LoopThroughLists()
 End If
 End Sub

Sub SplitIds(ByVal ids As String())

Dim arrayString As [String]() = ids
 Dim splitted As New List(Of String())()
 'This list will contain all the splitted arrays.
 Dim lengthToSplit As Integer = 190

Dim arrayLength As Integer = arrayString.Length

Dim i As Integer = 0
 While i < arrayLength
 Dim val As String() = New String(lengthToSplit - 1) {}

If arrayLength < i + lengthToSplit Then
 lengthToSplit = arrayLength - i
 End If
 Array.Copy(arrayString, i, val, 0, lengthToSplit)
 splitted.Add(val)
 i = i + lengthToSplit
 End While

Dim WhatsTheLength As Integer = splitted.Count
 Dim j As Integer = 0
 Dim k As Integer = 0
 Dim objects As com.cvent.api.CvObject()

 If Not IsDBNull(ids) Then
 While k < WhatsTheLength
 objects = _ws.Retrieve(com.cvent.api.CvObjectType.Contacts, splitted(k)) 'drop the s in contacts, it was messing up the blog
 For j = 0 To objects.Length - 1

 Dim source As com.cvent.api.Contacts = objects(j) 'drop the s in contacts, it was messing up the blog
 If source.FirstName = "" Then

Else
 If source.SourceId = "" Then
 NoSourcelist.Add(source.FirstName & " " & source.LastName & "(in Cvent)")
 Else
 Cventlist.Add(source.SourceId)
 End If
 End If
 Next j
 k = k + 1
 End While
 End If

LoopThroughLists()

End Sub

Now that we have a list of people that should be in the group (from our SQL) and people that are in the group (from Cvent) we can compare them. Since you can’t do a compare with list (of T) I loop through each list and see if that memberid is in the other list, this gives me an oput put of who isn’t in each list, I then know who needs deleted from the Cvent group (because they shouldn’t be there) and who needs added to the cvent group (because they’re in our SQL group)


Sub LoopThroughLists()
Dim CventListID As Integer
 For Each objectItem As Object In Cventlist

 CventListID = objectItem

 Dim IsInCventresult As Integer = SQLlist.Find(AddressOf FindIDinSQL)
 If InSQLReturned = "False" Then
 NotinSQL = NotinSQL & " " & getindividualnames(CventListID)

 End If
 Next

For Each objectSQLItem As Object In SQLlist

 SQLListID = objectSQLItem

 Dim IsInSQLresult As Integer = Cventlist.Find(AddressOf FindIDinCvent)
 If InCventReturned = "False" Then
 NotinCvent = NotinCvent & " " & getindividualnames(SQLListID)

 End If
 Next

End Sub

Private Function FindIDinSQL(ByVal bk As Integer) As Boolean
 'looks for cvent id in sql list
 If bk = CventListID Then
 InSQLReturned = "True"
 Return True
 Else
 InSQLReturned = "False"
 Return False
 End If
 End Function
 Private Function FindIDinCvent(ByVal bk1 As Integer) As Boolean
 'looks for sql id in cvent list
 If bk1 = SQLListID Then
 InCventReturned = "True"
 Return True
 Else
 InCventReturned = "False"
 Return False
 End If
 End Function

from here I now have my list of who to delete and who to add. For now I list them out and they can be manually done, my next step is to add a module to use the Cvent API add/delete those that are in each list (I just didn’t have time, since this was a something we need but do it between projects type of program)

     

Umbraco MSSQL Settings

Starting from nothing, I’ve never seen Umbraco or messed with it, so I figured the best way to learn is to install it. I installed Umbraco v6.1.6, the latest stable version.

My first install was through Microsofts Web Platform installer but 2 days after my install there was an update and I figured if I was going to be taking care of this beast, I should know how to do everything on it, so manual install it was.

I deleted everything and deleted the SQL database and started the manual install. Everything went as planned until I couldn’t connect to the sql server. I have other sites connecting to the server so I knew something was wrong with the user I created for the Umbraco DB.

After some seraching I came across a great walk through for the manual Umbraco install on Windows 2008 (works for Windows 2008 r2 also) http://our.umbraco.org/wiki/install-and-setup/how-to-install-umbraco-on-windows-server-2008

Step 4: Setup database

Open up SQL Server Management Studio and add a database by right-clicking on Databases and choosing New database. Give your database a name, and click “OK”.

The create a user for your database by going to Security and right-clicking on “Logins” and choosing “New login”. Give your user a name (for example: umbracouser), choose SQL Server authentication, and enter a password.

Then go to User Mapping and in the list that appears, check the database you just created. Then you check the following roles in the “Database role membership” list:

db_datareader

db_datawriter

db_ddladmin

db_securityadmin

public

Click “OK”.

After setting the user mappings to the correct settings, I could connect to the SQL server/database and I was next asked which starter kit I wanted.

And so the long road starts…..

     

Learning Umbraco

Work has decided that with taking care of our network, SQL Server, Webserver, doing all the development and admining all of this, that I don’t have enough under my belt. So I will be begining a deep dive into Umbraco. Umbraco is a CMS written in .net and runs on windows servers. As I slowly learn Umbraco, I’ll be posting what I’ve learned and any work arounds I come across.