Saturday, 5 July 2014

The DELETE statement conflicted with the REFERENCE constraint

You will get this error when you are trying to Delete the record from a Table which has a reference in another Table. Consider two tables Customers(Primary Table) and SalesHistory(Relative Table).
Fix: The DELETE statement conflicted with the REFERENCE constraint
Here the column CustomerID is Primary Key of the table Customers and which is referenced as Foreign Key column in SalesHistoryTable. IDREFERENCE constraint is "FK__SalesHist__Custo__060DEAE8".

Fix: The DELETE statement conflicted with the REFERENCE constraint

When we try to delete a row from Customers Table with CustomerID which is referenced in SalesHistory.
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2
We will get this SQL error:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK__SalesHist__Custo__060DEAE8". 
The conflict occurred in database "MorganDB", table "dbo.SalesHistory", column 'CustomerID'.
To solve this issue, we need to delete corresponding rows from SalesHistory Table first and then delete from Table Customers.(it means, we need to delete dependency first before deleting actual data)
Delete
  FROM [MorganDB].[dbo].[SalesHistory] where CustomerID=2
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2
Read More...
Blogger Tricks

Monday, 9 June 2014

Read and Write web.config file in C#

Read and Write web.config file is one of the important and regular task from server side code or from different application. We can Read and Write web.config's AppSetting or SQL ConnectionString using two classes WebConfigurationManager and ConfigurationManager in C#. In this article, I am write C# code examples to Read and Write Web config settings at runtime and from different application(i.e -Setup).

Read web.config file in C#

Consider the sample web.config file structure:
<configuration>
  <appSettings>
    <add key="appPath" value="C:\Prgram Files\MyApplication" />
  </appSettings>
  <connectionStrings>
    <add name="sqlInfo" connectionString="Data Source=.\SQLExpress; 
Initial Catalog=MorganDB2; Integrated Security=SSPI;" />
  </connectionStrings>
  </configuration>

Read web.config's AppSetting and ConnectionString using WebConfigurationManager:

private void ReadWebConfigSetting()
{
    string appPath = WebConfigurationManager.AppSettings["appPath"];
    string connString = WebConfigurationManager.ConnectionStrings["sqlInfo"].ToString();
}

Read web.config's AppSetting and ConnectionString using ConfigurationManager:

private void ReadWebConfigSettingbyCM()
{
    string appPath = ConfigurationManager.AppSettings["appPath"];
    string connString = ConfigurationManager.ConnectionStrings["sqlInfo"].ToString();
}

Write web.config setting using C#

Write web.config's AppSetting and ConnectionString from Different Application:

Use the below sample code to write or edit a web.config's AppSetting and ConnectionString from different/other location.
private static void EditWebConfigFile()
{
    string webDirPath = @"C:\Prgram Files\MyApp\WebFiles";
    var vdm = new VirtualDirectoryMapping(webDirPath, true, "web.config");
    var wcfm = new WebConfigurationFileMap();
    wcfm.VirtualDirectories.Add("/", vdm);
    var configuration = WebConfigurationManager.OpenMappedWebConfiguration(wcfm, "/");

    var appSettingsSection = (AppSettingsSection)configuration.GetSection("appSettings");
    appSettingsSection.Settings["appPath"].Value = @"C:\Prgram Files\MyApp";

    var connStrSection = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    connStrSection.ConnectionStrings["sqlInfo"].ConnectionString = @"Data Source=.\SQLExpress;
 Initial Catalog=MorganDB; Integrated Security=SSPI;";

    configuration.Save(ConfigurationSaveMode.Modified);
}

Write web.config's AppSetting and ConnectionString at Run Time:

Use the below sample code to write or edit a web.config's AppSetting and ConnectionString at run time.
private void WriteWebConfigSettings()
{
    var configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");

    var appSettingsSection = (AppSettingsSection)configuration.GetSection("appSettings");
    appSettingsSection.Settings["appPath"].Value = @"C:\Prgram Files\MyApplication";

    var connStrSection = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    connStrSection.ConnectionStrings["sqlInfo"].ConnectionString = @"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB2; Integrated Security=SSPI;"; ;

    configuration.Save(ConfigurationSaveMode.Modified);
}

Read More...

Wednesday, 4 June 2014

Timer in JavaScript

We have following three built-in JavaScript functions to execute a JavaScript function or block of code with regular interval by specified delay.

1. setTimeOut()

The setTimeout() function is commonly used to call a function for one time after the specified delay.
<html>
<head>
<script>

var timerId
function updateTime() {
 var date = new Date()

  var hours = date.getHours()
  if (hours < 10) hours = '0'+hours
  document.getElementById('hour').innerHTML = hours

  var minutes = date.getMinutes()
  if (minutes < 10) minutes = '0'+minutes
  document.getElementById('min').innerHTML = minutes

  var seconds = date.getSeconds()
  if (seconds < 10) seconds = '0'+seconds
  document.getElementById('sec').innerHTML = seconds
  
  timerId = setTimeout(updateTime, 1000)
}

function startClock() {  
  if (timerId) return
  updateTime()
}

function stopClock() {
  clearTimeout(timerId)
  timerId = null
}
</script>
</head>
<body>

<label id="hour">hh</label>:<label id="min">mm</label>:<label id="sec">ss</label>
<input type="button" onclick="startClock()" value="Start">
<input type="button" onclick="stopClock()" value="Stop">

</body>
</html>

2. setInterval()

The setInterval() function is commonly used to execute a function repeatedly in the specified interval.
<html>
<head>
<script>

var timerId
function updateTime() {
 var date = new Date()

  var hours = date.getHours()
  if (hours < 10) hours = '0'+hours
  document.getElementById('hour').innerHTML = hours

  var minutes = date.getMinutes()
  if (minutes < 10) minutes = '0'+minutes
  document.getElementById('min').innerHTML = minutes

  var seconds = date.getSeconds()
  if (seconds < 10) seconds = '0'+seconds
  document.getElementById('sec').innerHTML = seconds  
}

function startClock() {  
  if (timerId) return

  timerId = setInterval(updateTime, 1000)
  updateTime()  // start immediately, don't wait 1 sec until setInterval triggers  
}

function stopClock() {
  clearInterval(timerId)
  timerId = null
}
</script>
</head>
<body>

<label id="hour">hh</label>:<label id="min">mm</label>:<label id="sec">ss</label>
<input type="button" onclick="startClock()" value="Start">
<input type="button" onclick="stopClock()" value="Stop">

</body>
</html>

3. requestAnimationFrame()

The window.requestAnimationFrame() method tells the browser that you wish to perform an animation and requests that the browser call a specified function to update an animation before the next repaint. The method takes as an argument a callback to be invoked before the repaint.

Refer this nice example: http://jsfiddle.net/XQpzU/4358/light/

Read More...

Friday, 30 May 2014

DELETE vs TRUNCATE in SQL Server

Delete command removes the specific set of rows from a table based on the condition in a WHERE clause. Truncate command removes all the rows from a table and there will be no data in the table after we run the truncate command.

DELETE:

  • DELETE removes rows and records an entry in the transaction log for each deleted row. 
  • DELETE does not reset identity counter of the table.
  • DELETE can be used with or without a WHERE clause
  • DELETE Activates Triggers. 
  • DELETE can be rolled back.
  • DELETE is DML Command. 

DELETE command syntax:

Use [MorganDB]
GO
Delete EMPLOYEES Where Age<18 data-blogger-escaped-pre="">

TRUNCATE:

  • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. 
  • TRUNCATE will not support condition based delete.
  • TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. 
  • TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, and indexes  are remains same.
  • Cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. 
  • TRUNCATE cannot be rolled back. TRUNCATE is DDL Command. 
  • TRUNCATE Resets identity counter of the table

TRUNCATE command syntax:

Use [MorganDB]
GO
Truncate Table EMPLOYEES 
Thanks, Morgan Software Developer
Read More...

Event 17058 - initerrlog: Could not open error log file (SQL Server)

I got an error when I try to start the SQL Server service and the error message suggest me to analyze event log for further details, after I have analyzed found the following details in the event 17058.
Log Name:      Application
Source:        MSSQLSERVER
Event ID:      17058
Task Category: Server
Level:         Error
Description:
initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.).

Cause:

This error occurs due to the insufficient privilege of SQL Service Service Account in the Log directory : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

Troubleshooting and Fix:

1. Start SQL Server Configuration manager.
2. Click to select 'SQL Server Services' from the left menu options.
3. On the right panel, right click on 'SQL Server (MSSQLSERVER)' and click 'Properties'.
4. Click 'Advanced' tab.
5. Scroll down and copy the value stored in 'Startup Parameters' and paste it on textfile. It will be something like this :
-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Event 17058 - initerrlog: Could not open error log file (SQL Server)

6. Browse the Log location "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log".
7. Right click on the folder "Log" and click Properties and then to visit to "Security" tab. Now, check SQL Server service account permission on this folder and give proper access to this folder.
8. Now, Restart the SQL Server service. if you face the same error again, try to change highly privileged service account like "Local System".

Thanks,
Morgan
Software Developer
Read More...

Tuesday, 27 May 2014

How to Store and Retrieve File in SQL Server Database using C# .Net

Description:

In this article I am going to write C# code to insert/save/store any type of file (pdf/txt/image/.zip) into Sql server database and then retrieve/read file from Sql server database using Binary datatype.

In SQL Server we have available datatypes to store string text, int, bool, datatime and even xml. But we don't have any provision to store some complex structured data like ZIP file and PDF file. To overcome this, we have the special datatype varbinary, this is C#'s datatype Byte Array equivalent in SQL Server. In this article, I am going write C# example to convert file into Byte Array and Insert/Store/Save Byte [] into SQL Server table, Read/Retrieve Byte [] data from SQL Server table and Convert into original file.

Summary:


Store/Insert File into SQL Server Database as Binary datatype

 We are doing two processes to store/save file into SQL Server table.
      i. Convert file content into Byte Array(Byte [])
      ii. Insert file content's Byte Array into Sql Server

Consider the text file sample.txt.
How to Store/Insert/Save and Retrieve/Read/Export File in SQL Server using C# .Net

public static void InsertFileintoSqlDatabase()
{
    string filePath = @"C:\sample.txt";

    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [MyTable](ID int, [FileData] varbinary(max))";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        // Converts text file(.txt) into byte[]
        byte[] fileData = File.ReadAllBytes(filePath);

        string insertQuery = @"Insert Into [MyTable] (ID,[FileData]) Values(1,@FileData)";

        // Insert text file Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@FileData", fileData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read/Export File from SQL Server Database 

Use the below C# code to export/read/retrieve text file from SQL Server table that was stored as binary type and to save/export as new text file.
public static void ExportFileFromSqlDatabase(int ID)
        {
            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
            {
                sqlconnection.Open();

                string selectQuery = string.Format(@"Select [FileData] From [MyTable] Where ID={0}"
                                    , ID);

                // Read File content from Sql Table 
                SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    byte[] fileData = (byte[])reader[0];
                    // Write/Export File content into new text file
                    File.WriteAllBytes(@"C:\New_Sample.txt", fileData);
                }
            }
        }
New/Exported file output:
How to Store/Insert/Save and Retrieve/Read/Export File in SQL Server using C# .Net

 Varbinary is recommended datatype to store any kind of file in MS Sql Server.

Thanks,
Morgan,
Software Developer
Read More...

Thursday, 22 May 2014

Difference between Integrated Security SSPI vs True

We can use two kinds of Authentication to connection SQL Server namely SQL Server Authentication and Windows Authentication. To force Windows Authentication, we normally use either the parameter Integrated Security=True or Integrated Security=SSPI. But some of us(at least people I know) don't know what is real difference between Integrated Security SSPI vs True.

After I have analyzed some time in web, found following tips.
Actually they are not the same or interchangeable, Microsoft says they are equivalent but that doesn't mean interchangeable or that they are the same thing. TRUE ignores User Id and Password if provided and uses those of the running process, SSPI it will use them if provided which is why MS prefers this. They are equivalent in that they use the same security mechanism to authenticate.
Source: http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi#comment-6483379

Thanks,
Morgan
Software Developer
Read More...

Wednesday, 21 May 2014

How to Store and Read Byte Array in SQL Server Database using C# .Net

Description:

Byte is an immutable value type that represents unsigned integers with values that range from 0 to 255. You can almost convert any kind of data into Byte Array(Byte []) like File, Image, Xml and etc..In SQL Server, we have enough datatypes to store string text, int, bool, datatime and even Xml. But we don't have any provision to store some complex structured data like ZIP file and PDF file. To overcome this, we have the special datatype varbinary, this is C#'s datatype Byte Array equivalent in SQL Server. In this article, I am going write C# example to Insert/Store/Save Byte [] into SQL Server table and Read/Retrieve Byte [] data from SQL Server table.

Summary:


Insert Byte Array into SQL Server Table using C# .NET

Use the below C# function to store/save Byte [] into SQL Server table as Binary datatype.
public static void InsertByteArrayintoSqlDatabase()
{
    string sampleText = "Hello World!";

    byte[] byteData = Encoding.UTF8.GetBytes(sampleText);

    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [MyTable](ID int, [BinData] varbinary(max))";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        string insertXmlQuery = @"Insert Into [MyTable] (ID,[BinData]) Values(1,@BinData)";

        // Insert Byte [] Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@BinData", byteData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read Byte Array from SQL Server Database using C# .NET

Use the below C# code to read/retrieve Byte [] from SQL Server table that was stored as binary type.
public static void ReadByteArrayFromSqlDatabase(int id)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        string selectQuery = string.Format(@"Select [BinData] From [MyTable] Where ID={0}",id);

        // Read Byte [] Value from Sql Table 
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            byte[] byteData = (byte[])reader[0];
            string strData = Encoding.UTF8.GetString(byteData);
            Console.WriteLine(strData);
        }
    }
}

How to Insert and Read Byte Array into SQL Server Table using C# .Net

You can almost save any kind of data using varbinary datatype and even though we have image datatype, varbinary is the recommended datatype to store image in sql server instead of image datatype. check this link: http://msdn.microsoft.com/en-us/library/ms187993.aspx

Thanks,
Morgan,
Software Developer
Read More...

Wednesday, 14 May 2014

Add AD Group Members using Powershell Script

Description:

In this article, I am going to write Powershell script samples to Add members to Active Directory Group, Add Group members by importing members from other AD Groups and Add AD Security Group members From CSV file.
You can Add Group members by using the Active Directory powershell cmdlet Add-ADGroupMember.
Add-ADGroupMember [-Identity] <ADGroup> [-Members] <ADPrincipal[]>
The Identity parameter specifies the Active Directory group that receives the new members. You can identify a group by its distinguished name (DN), GUID, SID or SamAccountName.

The Members parameter specifies the new members to add to a group. You can identify a new member by its distinguished name (DN), GUID, SID or SamAccountName.

Summary:

Add Active Directory Group Members using Powershell Script

Add user accounts to AD Group by samAccountName:
Import-Module ActiveDirectory
Add-ADGroupMember "Domain Admins" "MorganTest1,MorganTest2";
Add AD Group members by distinguished name (DN):
Import-Module ActiveDirectory
Add-ADGroupMember "Domain Admins" "CN=MorganTest1,OU=London,DC=TestDomain,DC=local";

Add Members to AD Group by Importing Members from other Group using Powershell

By using above examples, you can easily add the group members to AD group. Providing and changing permissions to AD security object is inevitable in this dynamic world. So in some cases, you may be in the need of adding new group members by importing members from other existing Active Directory group. Use the below powershell script to achieve this need.

Steps to Import existing Group members to other AD Group:
   1. Copy the below Powershell script and paste in Notepad file.
   2. Change the value for the variables $existingGroup and $newGroup with your own AD Group which you want to import and add group members
   3. SaveAs the Notepad file with the extension .ps1 like Import-Add-Group-Members.ps1

Powershell script file: Download Import-Add-Group-Members.ps1
Import-Module ActiveDirectory
  $existingGroup = "Domain Admins"
  $newGroup = "Powershell Admins"
 Get-ADGroupMember $existingGroup  | ForEach-Object {
   $samAccountName = $_."samAccountName" 
   Add-ADGroupMember $newGroup $samAccountName;
   Write-Host "- "$samAccountName" added to "$newGroup
}
   4. Now run the file Import-Add-Group-Members.ps1 from Powershell to Import members from existing AD Group and add as members of other AD Group.
PS C:\Scripts> .\Import-Add-Group-Members.ps1
Add Active Directory Group Members using Powershell Script

Add Members to AD Group by Importing Members From CSV using Powershell Script

   1. Consider the CSV file Users.csv which contains set of Active Directory users to add as members to AD Group with the attribute samAccountName.
Disable Active Directory User Account using Powershell Script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the Users.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Import-AD-Group-Members-From-CSV.ps1

Powershell script file: Download Import-AD-Group-Members-From-CSV.ps1
Import-Module ActiveDirectory
  $adGroup = "Powershell Admins"
Import-Csv "C:\Scripts\Users.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 
 Add-ADGroupMember $adGroup $samAccountName;
 Write-Host "- "$samAccountName" added to "$adGroup
}
   5. Now run the file Import-AD-Group-Members-From-CSV.ps1 from Powershell to Import Bulk Active Directory users from CSV and add as member to AD Security Group.
PS C:\Scripts>  .\Import-AD-Group-Members-From-CSV.ps1
Add Members to AD Group by Importing Members From CSV using Powershell Script

Note: I have placed script file in the location C:\Scripts, if you placed in any other location, you can navigate to that path using CD path command (like cd "C:\Downloads").



Thanks,
Morgan
Software Developer
Read More...

Enable AD User Account using Powershell Script

Description

In this article, I am going give powershell script examples to Enable Active Directory user account by user's samAccountName and DistinguishedName, Enable AD Users from specific OU, and Enable Bulk AD users from CSV file using powershell script.

You can Enable an AD Account by using the Active Directory powershell cmdlet Enable-ADAccount.
Enable-ADAccount -Identity <adaccount>
The Enable-ADAccount cmdlet enables an Active Directory user, computer, or service account. The Identity parameter specifies the Active Directory user, computer service account, or other service account which you want to enable. You can identify an account by its distinguished name (DN), GUID, security identifier (SID), or samAccountName.

Summary:


Enable AD User Account with samAccountName

Import-Module ActiveDirectory
Enable-ADAccount -Identity MorganTest

Enable AD User Account with DistinguishedName

Import-Module ActiveDirectory
Enable-ADAccount -Identity "CN=MorganTest,OU=London,DC=TestDomain,DC=local"

Enable Active Directory Users from Specific OU

Import-Module ActiveDirectory
Get-ADUser -Filter 'Name -like "*"' `
  -SearchBase "OU=London,DC=TestDomain,DC=local" | Enable-ADAccount

Enable Bulk AD Users from CSV file using Powershell Script

   1. Consider the CSV file Users.csv which contains set of Active Directory users to enable with the attribute samAccountName.
Disable Active Directory User Account using Powershell Script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the Users.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Enable-Bulk-AD-Users-FromCSV.ps1

Powershell script file: Download Enable-Bulk-AD-Users-FromCSV.ps1
Import-Module ActiveDirectory
Import-Csv "C:\Scripts\Users.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 
Get-ADUser -Identity $samAccountName | Enable-ADAccount
Write-Host "-User "$samAccountName" Enabled"
}
   6. Now run the Enable-Bulk-AD-Users-FromCSV.ps1 file in Powershell to Enable Bulk Active Directory users from CSV file.
PS C:\Scripts>  .\Enable-Bulk-AD-Users-FromCSV.ps1
Enable Bulk AD Users From CSV file using Powershell Script

Note: I have placed script file in the location C:\Scripts, if you placed in any other location, you can navigate to that path using CD path command (like cd "C:\Downloads").



Thanks,
Morgan
Software Developer
Read More...

Monday, 12 May 2014

How to Pin a Program to Start menu via Group Policy

Description:

   In this article, I am going write step-by-step guide for how to Pin a Program to Start menu via GPO. There is no direct Group Policy Preference setting to Pin Items to Start menu via GPO for all users. We are going to achieve via Logon Script. before start to read, pleas click to download vbscript: Download PinItems . Here, I have explained two processes, Pin a Program to Start menu in Local Machine and Pin a Program/Item to Start menu through GPO for all users.

Summary:


How to Pin Programs to Start menu in Windows 7

   In Windows 7, Start Menu pinned items are stored in the following location:

%APPDATA%\Microsoft\Internet Explorer\Quick Launch\User Pinned\StartMenu

Steps to Pin Programs/Items to Start menu in Current Machine:

1.  Click to download the vbscript file PinItems Download PinItems.
2.  Place the downloaded vbscript file in your favourite place. I have placed here -> C:\Scripts\PinItem.vbs
3.  Now run the following command in Command Prompt to Pin MS Paint Program to Start menu.
cscript C:\Scripts\PinItem.vbs  /item:"c:\windows\system32\mspaint.exe"


How to Pin a Program to Start menu via Group Policy

4. Then go the Start menu pinned items location : %APPDATA%\Microsoft\Internet Explorer\Quick Launch\User Pinned\StartMenu and you could see the shortcut icon for paint program.
How to Pin a Program to Start menu via Group Policy



Note: Here, you can replace your own program's exe path that you are going to pin it in Start menu.
Ex: Use the below command to pin uTorrent in Start menu.
cscript C:\Scripts\PinItem.vbs /item:"C:\Program Files\uTorrent\uTorrent.exe"

How to Pin a Program to Start menu via GPO

   After read the above steps, you may guess what we are going to do to Pin a Program/Items to Start menu via Group Policy. We are going save the above commands in a script or batch file and run through GPO's logon script to Pin a Programs to Start menu for all user's computer.

Steps to Pin a Program/Items to Start menu through GPO:

1. Click to download the vbscript file PinItems Download PinItems.

2. Place the downloaded vbscript file in any share path(Network Share). I have placed here -> \\work2008R2\share\PinItem.vbs

3. Open new Notepad file, copy the below command and paste in Notepad.
cscript C:\Scripts\PinItem.vbs /item:"c:\windows\system32\mspaint.exe"
4. Save the Notepad file with the extension .bat like PinProgram.bat

5. Place the PinProgram.bat file into same share path where you placed PinItem.vbs (\\work2008R2\share\PinProgram.bat)


How to Pin a Program/Items to Start menu via GPO

6. Open the Group Policy Management console by running the command gpmc.msc.

7. Expand the tree and Right-click on the OU you want this policy to be applied to. Now, I am going to apply users who are under the OU DesignTeam. so right-click on the OU DesignTeam, and click Create a GPO in this domain, and Link it here...

How to Pin a Program to Start menu via GPO

8. Type new policy name and click OK. Here, I am giving policy name Pin-StartMenu-Program-Policy

Steps to Pin a Program to Start menu via GPO

9. Now right-click on the newly created GPO Pin-StartMenu-Program-Policy and click edit.



Steps to Pin a Program to Start menu through GPO

10. Go to the settings Scripts under User Configuration (User Configuration->Policies->Windows Settings->Scripts(Logon/Logoff)). In the right side pane, click Logon.


Steps to Pin a Program/Items to Start menu through GPO

11. Add the batch file path \\work2008R2\share\PinProgram.bat in logon Script Name and click OK.


Steps to Pin a Program/Items to Start menu through GPO

12. Click Apply to apply Logon Script settings.


Steps to Pin a Program/Software/Application to Start menu through GPO

13.Run the command gpupdate /force to apply gpo to all the users under the OU DesignTeam.

How to Pin a Application/Program to Start menu via GPO

Now, you have configured new GPO to Pin a Program/Item to Start menu via GPO in all user's computer. when a user login, this script automatically run and Pin programs to Start menu.

See the below image which I got when login as the user Morgan:

Pin a Application/Program to Start menu via GPO

Related Articles:

Create Folder on Desktop via GPO
Add Environment Variable via Group Policy
Add desktop shortcut icon through Group Policy

Thanks,
Morgan
Software Developer
Read More...

Thursday, 8 May 2014

Powershell Script: Create Fine-Grained Password Policy and Apply to Group and Users

Description:

In this article, I am going to give powershell script examples to create Fine Grained Password Policy or Custom Password Policy and explain how to link/apply a fine grained password policy to specific user or a group using Powershell.

Create Fine Grained Password Policy using Powershell Script

We can use the powershell cmdlet New-ADFineGrainedPasswordPolicy to create a new Active Directory fine grained password policy. Follow the below steps to run the below Powershell script that will create new fine grained password policy with the name AdminUserPSO.
   1. Copy the below powershell script and paste in Notepad file.
   2. Change the new policy name AdminUserPSO into your own password policy name which you want to create.
   4. SaveAs the Notepad file with the extension .ps1 like Create-Fine-Grained-PasswordPolicy.ps1

Powershell script file: Download Create-Fine-Grained-PasswordPolicy.ps1
Import-Module ActiveDirectory
New-ADFineGrainedPasswordPolicy -Name "AdminUsersPSO" `
 -Precedence 500 `
 -ComplexityEnabled $true `
 -Description "The ADmin Users Password Policy" `
 -DisplayName "Admin Users PSO" `
 -LockoutDuration "0.14:00:00" `
 -LockoutObservationWindow "0.00:15:00" `
 -LockoutThreshold 10 `
 -MaxPasswordAge "45.00:00:00" `
 -MinPasswordAge "1.00:00:00" `
 -MinPasswordLength 8 `
 -PasswordHistoryCount 24 `
 -ReversibleEncryptionEnabled $false 
Write-Host "----New Password Policy 'AdminUsersPSO' createted----"
   5. Now run the Create-Fine-Grained-PasswordPolicy.ps1 file in Powershell to create a new Active Directory fine grained password policy using following command
PS C:\Scripts> .\Create-Fine-Grained-PasswordPolicy.ps1
Powershell Script to Create Fine Grained Password Policy

Note: I have placed script file in the location C:\Scripts, if you placed in any other location, you can navigate to that path using CD path command (like cd "C:\Downloads").

Apply Fine Grained Password Policy to Group and Users using Powershell Script

You can use the powershell cmdlet Add-ADFineGrainedPasswordPolicySubject to apply a fine-grained password policy to one or more global security groups and users.

Use below script to apply the fine-grained password policy AdminUserPSO to the group Administrators:
Add-ADFineGrainedPasswordPolicySubject AdminUsersPSO -Subjects 'Administrators'
Use below script to apply the fine-grained password policy AdminUserPSO to the users Admin and MorganTest:
Add-ADFineGrainedPasswordPolicySubject AdminUsersPSO -Subjects Admin,MorganTest
Use below script to find all the groups and users to which the fine-grained password policy AdminUserPSO applies:
Get-ADFineGrainedPasswordPolicy AdminUsersPSO | ft AppliesTo -A
Create Fine-Grained Password Policy and Apply to Group and Users using Powershell Script


Thanks,
Morgan
Software Developer
Read More...

Tuesday, 6 May 2014

Powershell Script to Disable AD User Account

Description:

In this article, I am going give powershell script examples to disable Active Directory user account by user's samAccountName and DistinguishedName, disable AD Users from specific OU, and disable Bulk AD users from CSV file using powershell script.

You can disable an ad account by using the Active Directory powershell cmdlet Disable-ADAccount.
Disable-ADAccount -Identity <adaccount>
The Disable-ADAccount cmdlet disables an Active Directory user, computer, or service account. The Identity parameter specifies the Active Directory user, computer service account, or other service account that you want to disable. You can identify an account by its distinguished name (DN), GUID, security identifier (SID), or samAccountName.

Summary:


Disable AD User Account with samAccountName

Import-Module ActiveDirectory
Disable-ADAccount -Identity MorganTest

Disable AD User Account with DistinguishedName

Import-Module ActiveDirectory
Disable-ADAccount -Identity "CN=MorganTest,OU=London,DC=TestDomain,DC=local"

Disable Active Directory Users from Specific OU

Import-Module ActiveDirectory
Get-ADUser -Filter 'Name -like "*"' `
  -SearchBase "OU=London,DC=TestDomain,DC=local" | Disable-ADAccount

Disable Bulk AD Users from CSV file using Powershell Script

   1. Consider the CSV file Users.csv which contains set of Active Directory users to disable with the attribute samAccountName.
Disable Active Directory User Account using Powershell Script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the Users.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Disable-Bulk-AD-Users-FromCSV.ps1

Powershell script file: Download Disable-Bulk-AD-Users-FromCSV.ps1
Import-Module ActiveDirectory
Import-Csv "C:\Scripts\Users.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 
Get-ADUser -Identity $samAccountName | Disable-ADAccount
}
   6. Now run the Disable-Bulk-AD-Users-FromCSV.ps1 file in Powershell to Disable Bulk Active Directory users from CSV file.
PS C:\Scripts>  .\Disable-Bulk-AD-Users-FromCSV.ps1
Disable Bulk AD Users From CSV File using Powershell Script

Note: I have placed script file in the location C:\Scripts, if you placed in any other location, you can navigate to that path using CD path command (like cd "C:\Downloads").



Thanks,
Morgan
Software Developer
Read More...

Monday, 5 May 2014

Event ID 4768 - A Kerberos authentication ticket (TGT) was requested

Description:

   Event ID 4768 is logged on domain controllers only for both success and failure instances. If the username and password are correct and the DC grants the TGT and logs the Event ID 4768 (authentication ticket granted). If the ticket request fails Windows will either log the event 4768 with failure as the type or 4771. In this article, I am going to explain about how to enable or configure Event ID 4768 through Default Domain Controller Policy GPO and Auditpol.exe, and how to disable Event ID 4768.

Summary:


Event ID 4768 Source:

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          5/5/2014 3:43:20 PM
Event ID:      4768
Task Category: Kerberos Authentication Service
Level:         Information
Keywords:      Audit Success
User:          N/A
Computer:      Work2008R2.TestDomain.local
Description:
A Kerberos authentication ticket (TGT) was requested.

Account Information:
 Account Name:  LTest
 Supplied Realm Name: TESTDOMAIN
 User ID:   TESTDOMAIN\LTest

Service Information:
 Service Name:  krbtgt
 Service ID:  TESTDOMAIN\krbtgt

Network Information:
 Client Address:  192.78.2.145
 Client Port:  0

Additional Information:
 Ticket Options:  0x40810010
 Result Code:  0x0
 Ticket Encryption Type: 0x12
 Pre-Authentication Type: 2

Certificate Information:
 Certificate Issuer Name:  
 Certificate Serial Number: 
 Certificate Thumbprint:  

Certificate information is only provided if a certificate was used for pre-authentication.

Pre-authentication types, ticket options, encryption types and result codes are defined in RFC 4120.

Enable AD Logon Audit Event 4768 via Group Policy

    To enable event id 4768  in every Domain Controller, We need to configure audit settings in Default Domain Controllers Policy, or you can create new GPO and links it to the Domain Controllers OU via GPMC console, or else you can configure the corresponding policies on Local Security Policy of each and every Domain Controller..

Follow the below steps to enable Active Directory Kerberos Logon Audit event 4768 via Default Domain Controllers Policy.

    1. Press the key 'Window' + 'R'
    2. Type the command gpmc.msc, and click OK.
         Note: Skip the above steps by clicking Start -->Administrative Tools -->Group Policy                            Management.
    3. Expand the domain node and Domain Controllers OU,  right-click on the Default Domain Controllers Policy, then click Edit. - refer the below image.

Steps to enable Active Directory Kerberos Logon Audit Event ID 4768

    4. Expand Computer Configuration node and Security Settings and navigate to the node Audit Policy (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon).

Steps to enable/configure Active Directory Kerberos Logon Audit Event ID 4768

    5. In right-side pane, double-click on Audit account logon events and set Success and Failure setting to enable kerberos logon event 4768.
Steps to enable/configure Active Directory Kerberos Logon Audit Event ID 4768

   Note: In Windows 2008 R2 and later versions, you can also control this event by subcategory-level setting via Advanced Audit Policy Configuration.

    Expand Computer Configuration and Security Settings and navigate to the node Account Logon (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon) and set the setting Audit Kerberos Authentication Service as Success and Failure

Steps to enable/configure Event ID 4768

    6. Run the command gpupdate /force from command prompt to update Group Policy settings.


Enable/Configure Event ID 4768 via Auditpol

Auditpol.exe is the command line utility tool to change Audit Security settings as category and sub-category level. It is available by default Windows 2008 R2 and later versions/Windows 7 and later versions.

By using Auditpol, we can get/set Audit Security settings per user level and computer level.

Note: You should run Auditpol command with elevated privilege (Run As Administrator);

You can enable Event ID 4768 through Kerberos Authentication Service subcategory by using the following command

Success Audit:
auditpol /set /subcategory:"Kerberos Authentication Service" /success:enable
Steps to enable/configure Event ID 4768
Failure Audit:
auditpol /set /subcategory:"Kerberos Authentication Service" /Failure:enable
To update or refresh GPO settings, run the command gpupdate/force

How to disable/stop Event ID 4768

You can disable or stop the audit Event ID 4768 by removing success and failure audit of Kerberos Authentication Service subcategory by using the following command.
auditpol /set /subcategory:"Kerberos Authentication Service"
 /success:disable
You can also stop this event by removing the success and failure setting from the Default Domain Controller Policy's category level setting path (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon->Audit account logon events

 or by subcategory level setting (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon->Audit Kerberos Authentication Service)


Note: This article is applies to only Windows Server 2008 R2, Windows Server 2012, Windows 7 and Windows 8


Thanks,
Morgan
Software Developer
Read More...

Sunday, 4 May 2014

How to Store and Read C# Class Object in Sql Server

Description:

There is no build-in sql datatype to store C# class object. But we can save C# class object either by Xml or Binary column. We can't have more control with C# class properties when we store in binary column. So the better way is Xml column. We can use XQuery in Sql Server table to query by class object's properties. In this article I am going write about how to Store/Save/Insert and Retrieve/Read C# class object into Sql database table and how to write XQuery with Sql query to work with C# class object properties.

Summary:


Store/Insert C# Class Object into Sql Server Table as XMl value

You can store/insert/save C# class object into Sql Server Table by using below C# functions. Here we are using XmlSerializer to convert C# class object into XML string.
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Serialization;

namespace SQLSamples
{
    public class UserDetail
    {
        public string UserName { get; set; }
        public string MailID { get; set; }
        public string City { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            UserDetail userDetail = new UserDetail { UserName = "Morgan", 
               MailID = "Morgan@Domain.Com", City = "London" };

            InsertClassObjectIntoSQLTable(1, userDetail);
        }

        static void InsertClassObjectIntoSQLTable(int userID,UserDetail userDetail)
        {
            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
            {
                sqlconnection.Open();

                // create table if not exists 
                string createTableQuery = @"Create Table [UserTable] (ID int, [UserObject] xml)";
                SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
                command.ExecuteNonQuery();

                // Convert C# class object into xml string 
                string xmlData = ConvertObjectToXMLString(userDetail);

                string insertQuery = @"Insert Into [UserTable] (ID,[UserObject])
                                                 Values(1,@UserObject)";

                // Insert XMl Value into Sql Table by SqlParameter
                SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconnection);
                SqlParameter sqlParam =insertCommand.Parameters.AddWithValue("@UserObject",xmlData);
                sqlParam.DbType = DbType.Xml;
                insertCommand.ExecuteNonQuery();
            }
        }

        static string ConvertObjectToXMLString(object classObject)
        {
            string xmlString = null;
            XmlSerializer xmlSerializer = new XmlSerializer(classObject.GetType());
            using (MemoryStream memoryStream = new MemoryStream())
            {
                xmlSerializer.Serialize(memoryStream, classObject);
                memoryStream.Position = 0;
                xmlString = new StreamReader(memoryStream).ReadToEnd();
            }
            return xmlString;
        }
    }
}

Retrieve/Read C# Class Object from Sql Server Table

You can retrieve/read C# class object from Sql Server Table by using below C# functions. Here we are reading Xml value from Sql server and converting XML string into C# class object using XmlSerializer.
static void Main(string[] args)
{
    UserDetail userDetail = ReadClassObjectFromSqlServer(1);
    Console.WriteLine(userDetail.UserName);
}

public static UserDetail ReadClassObjectFromSqlServer(int userID)
{
    UserDetail userDetail = null;
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        string selectQuery = string.Format(@"Select [UserObject] From [UserTable] Where ID={0}"
                            , userID);

        // Read Xml Value from Sql Table 
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            string xmlValue = reader[0].ToString();
            userDetail = (UserDetail)ConvertXmlStringtoObject<UserDetail>(xmlValue);
        }
    }

    return userDetail;
}

static T ConvertXmlStringtoObject<T>(string xmlString)
{
    T classObject;

    XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
    using (StringReader stringReader = new StringReader(xmlString))
    {
        classObject = (T)xmlSerializer.Deserialize(stringReader);
    }
    return classObject;
}

XQuery to filter C# Objects by its properties

In some of the times, we might have a need to filter c# class objects by its properties from sql server itself. To achieve this we can use XQuery with Sql query to query Xml nodes.

Select all UserDetail class objects
Use [MorganDB]
Select * From [UserTable]
UserDetail class object Xml structure:
<UserDetail>
  <UserName>Morgan</UserName>
  <MailID>Morgan@Domain.Com</MailID>
  <City>London</City>
</UserDetail>
XQuery to filter by UserDetail's Property City:
Use [MorganDB]
Select * From [UserTable] Where UserObject.exist('/UserDetail[City="London"]')=1

How to Store and Retrieve C# Class Object into Sql Server table

XQuery to select only UserDetail's Property values:
Use [MorganDB]
Select ID,
   UserObject.value('(/UserDetail/UserName)[1]','nvarchar(250)') as UserName,
   UserObject.value('(/UserDetail/MailID)[1]','nvarchar(250)') as MailID,
   UserObject.value('(/UserDetail/City)[1]','nvarchar(250)') as City
 From [UserTable] 

How to Retrieve/Read C# Class Object from Sql Server table


Thanks,
Morgan
Software Developer
Read More...