Tuesday, 14 October 2014

Tacking AD Account Lockout/Logon Failure reason for Logon Type 3 and 8

Description:

Tracking reason for the Active Directory account lockout/logon failure is cumbersome task now a days. Unlike other normal logon types (Logon type 2 -Interactive Logon and Logon type 10 -Remote Logon), we can’t easily say/track the failure reason for the logon type 3 and 8. Because most of the time, the failures surrounded with these logon types are triggered or initiated by either saved/cached credentials or through third party tools. In this article, I am going to explain about Logon type 3 and Logon type 8 of the event 4625 and how to track the failure reason for these two logon types.

How to track AD account lockout/logon failure for Logon Type 3

This logon type occurs due to accessing a computer from elsewhere on the network (i.e Remote Desktop sharing tool), or accessing other resources like Network Share from elsewhere on the network by passing credentials. One of the most common sources of logon events with Logon type 3 is connections to shared folders or printers. But also other over-the-network logons are classed as logon type 3 as well as most logons to IIS except Basic authentication.

Consider following scenario:
      DC1         - Active Directory Domain Controller 
      Morgan-PC    - End user desktop computer
Now, when a user or any other applications tries to access resources like Network Share from Morgan-PC with wrong credentials, we will get the logon failure event 4625 with logon type 3 in DC1, it will points the machine Morgan-PC as Source Machine.

 Event 4625 for Logon Type 3:
Log Name:      Security
Date:          14-10-2014 03:43:55
Event ID:      4625
Task Category: Logon
Keywords:      Audit Failure
Computer:      DC1.TestDomain.Com
Description:
An account failed to log on.

Subject:
  Security ID:  NULL SID
  Account Name:  -
  Account Domain:  -
  Logon ID:  0x0

Logon Type:   3

Account For Which Logon Failed:
  Security ID:  NULL SID
  Account Name:  Morgan
  Account Domain:  TESTDOMAIN

Failure Information:
  Failure Reason:  Unknown user name or bad password.
  Status:   0xc000006d
  Sub Status:  0xc000006a

Network Information:
  Workstation Name: Morgan-PC
  Source Network Address: 212.158.1.110
  Source Port:  51283

How to track AD user lockout/logon failure for Logon Type 8

The logon type 8 occurs when the password was sent over the network in the clear text. Basic authentication in IIS is most possible cause for this kind of logon failure. As for as I know there are two commonly used Microsoft IIS based services with Basic Authentication by end users via either by their desktop or mobile device, such are OWA client and SharePoint server.

When an end-user connect the Basic authentication enabled OWA client from their desktop-pc/mobile device with wrong passwords, the event 4625 with logon type 8 will be logged in Exchange Server which hosts the OWA.

Consider the following scenario:
     DC1   - Active Directory Domain Controller 
     ExchSvr    - Exchange Server integrated with AD with OWA and DC1 as Authentication Server
     Morgan-PC/Mobile   - End user computer/mobile device
Now, when the user morgan tries to connect the OWA client from his desktop “Morgan-PC” with wrong password,
  • The logon failure event 4625 with logon type 8 will be logged in ExchSvr, and this event will points the Morgan-PC as Source Machine. 
  • Any one of these Authentication failure logon event (4768/4771/4776) will be logged in DC1 depends upon the authentication mechanism configured in AD, and this event will points the machine ExchSvr as Source Machine.
Log Name:      Security
Date:          10/14/2014 4:47:29 PM
Event ID:      4625
Task Category: Logon
Keywords:      Audit Failure
Computer:      ExchSVR.TestDomain.Com
Description:
An account failed to log on.

Subject:
  Security ID:  IIS APPPOOL\OWA
  Account Name:  OWA
  Account Domain:  IIS APPPOOL
  Logon ID:  0x215aa92

Logon Type:   8

Account For Which Logon Failed:
  Security ID:  NULL SID
  Account Name:  Morgan
  Account Domain:  TestDomain

Failure Information:
  Failure Reason:  Unknown user name or bad password.
  Status:   0xc000006d
  Sub Status:  0xc000006a

Process Information:
  Caller Process ID: 0xce4
  Caller Process Name: C:\Windows\System32\inetsrv\w3wp.exe

Network Information:
  Workstation Name: ExchSVR
  Source Network Address: 212.158.1.110
  Source Port:  40977

Detailed Authentication Information:
  Logon Process:  Advapi  
  Authentication Package: Negotiate
To track the starting point of this logon failure, we need to read events from two machines DC1 and ExchSVR.
  • By DC1 event, we can conclude the failure is triggered from ExchSVR
  • And then from ExchSVR event , we can conclude the actual failure was triggered from Morgan-PC (Source Network Address).


Thanks,
Morgan
Software Developer
Read More...
Blogger Tricks

Wednesday, 8 October 2014

Case Sensitive Search in SQL Query (Where)

Normally, in programming languages like C# .NET, we need to explicitly add criteria to ignore case sensitive search or compare operations but whereas in SQL Server, search operation by using where query is not case sensitive by default. So to make case sensitive where query, we need to add explicit criteria which is SQL Collation.

SQL Collation:

SQL Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. It encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet such as Latin1_General (the Latin alphabet used by western European languages).

Default Collation of the SQL Server installation is SQL_Latin1_General_CP1_CI_AS and this is not case sensitive. Consider the UserTable with following names Morgan morgan MorgaN
--Create Test Table
CREATE TABLE UserTable(
UserID int, UserName varchar(250))
 
--Insert rows into Table
Insert into UserTable values(1,'Morgan')
Insert into UserTable values(2,'morgan')
Insert into UserTable values(2,'MorgaN')
If we run below Query,
Select * from UserTable where UserName = 'morgan'
it will returns all the rows, since all the rows contains same data and record search is not case Sensitive.

Case Sensitive in SQL Search (Where) Query

 To get only case sensitive records you need to change collation of the UserName column. Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS and this is not case sensitive.

This is new query to get results with case sensitive operation.
Select * from UserTable where UserName COLLATE Latin1_General_CS_AS = 'morgan'

Case Sensitive in SQL Search (Where) Query
Thanks,
Morgan
Software Developer
Read More...

Tuesday, 29 July 2014

Create temp table in SQL Server

     SQL Server provides the feature temporary table which helps user to replace Stored Procedure that requires large number of row manipulation and to replace complex SQL Join operations. The temp table can be created at runtime and can do all kind of operations like normal table. But, as its name implies, the scope is limited.

SQL Server provides two types of temp tables based on its connection life time.

Local Temp Table

Local temp tables are only available to the current connection for the user, and are deleted when the connection is closed. it requires the single hash value "#" as the prefix when created.

Syntax: (CREATE TABLE #MyTempTable).
--Create Local Temp Table
CREATE TABLE #UserTempTable(
UserID int, UserName varchar(250), 
Email varchar(250))

--Insert rows into Local Temp Table
Insert into #UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Local Temp Table
Select * from #UserTempTable
How to Create temp table in SQL Server

Global Temp Table

Global Temp tables are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed. it requires the double hash value "##" as the prefix when created.

Syntax: (CREATE TABLE ##MyTempTable).
--Create Global Temp Table
CREATE TABLE ##UserTempTable(
UserID int,
UserName varchar(250), 
Email varchar(250))

--Insert rows into Global Temp Table
Insert into ##UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Global Temp Table
Select * from ##UserTempTable
Both Temporary tables are created in tempdb. Whenever we create new temp table, we can see it under Temporary folder of tempdb database.

Create Local temp table in SQL Server

Thanks,
Morgan
Software Developer
Read More...

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...

Monday, 9 June 2014

Read and Edit web.config file in C#

Read and Update 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 going to write C# code examples to Read and Edit 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#

Edit 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);
}

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

Use the below sample code to edit and update 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 

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...