Friday, 31 October 2014

ipconfig /release: An error occurred while releasing interface Loopback Pseudo-Interface 1

Today when I start and login into my laptop I have noticed that my laptop doesn't have Internet Access but it was connected with LAN network. Then I tried to disable and enable the LAN Network Adapter, now I am getting ip conflict message. So that I have decided to flushdns and release ip address. When I try to release IP by using ipconfig/release command, the following error occurs 'An error occurred while releasing interface Loopback Pseudo-Interface 1 : The system cannot find the file specified.'
C:\>ipconfig /release

Windows IP Configuration
An error occurred while releasing interface Loopback Pseudo-Interface 1 : The sy
stem cannot find the file specified.

The operation failed as no adapter is in the state permissible for
this operation.
After I have analyzed some time, found the following recommended solutions

Steps taken to fix issue in my laptop (windows 7):

- Changed the machine IP manually into temporary IP address.
- Disable and Enabled LAN Network Adapter.
- I got the Internet Access through my LAN Network.
- Restarted the machine.
- Run the commands ipconfig/flushdns and ipconfig/release
- Now changed the IP manually to my original IP address.

Other Recommended Solutions:

http://answers.microsoft.com/en-us/windows/forum/windows_vista-networking/an-error-occurred-while-releasing-interface/4261cacb-e924-4d74-a349-4f3b898c27a9

http://www.sevenforums.com/network-sharing/281902-unidentified-network-no-internet-access-lan.html

http://www.tomshardware.com/forum/34947-43-cannot-renew-release

http://www.reddit.com/r/techsupport/comments/1if7t4/connected_to_network_but_no_internet_access/

Read More...

Blogger Tricks

Sunday, 26 October 2014

AD PowerShell: Get-ADUser - Export/Select Properties Examples

The Active Directory PowerShell Get-ADUser cmdlet supports different default and extended properties. Refer this article Get-ADUser Default and Extended Properties for more details. Get-ADUser cmdlet also supports smart ldap filters and search scope to select only required users. In this article, I am going to write different examples to list AD user properties and Export AD User properties to CSV using PowerShell.

AD PowerShell: Get-ADUser - Select all properties

Use the below code to list all the supported AD user properties.
Import-Module ActiveDirectory
Get-ADuser -identity 'Morgan' -Properties *          
AD PowerShell: Get-ADUser - Select all properties

AD PowerShell: Get-ADUser - List Selected properties as Table format

This command lists the selected properties as table format of AD users whose City contains the text 'Austin'.
Import-Module ActiveDirectory
Get-ADUser -Filter 'City -like "*Austin*"' -Properties * |
 Select -Property Name,City,Mail,Department | FT -A          
AD PowerShell: Get-ADUser - List Selected properties as Table

AD PowerShell: Get-ADUser - Select users from specific OU

This command select all the AD users from the Organisation Unit 'Austin' and lists the selected properties.
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=Austin,DC=TestDomain,DC=Local" -Properties * |
 Select -Property Name,Mail,Department | FL           
AD PowerShell: Get-ADUser - Select users from specific OU

AD PowerShell: Get-ADUser - Export Selected properties to CSV file

This command export the selected proprties to CSV file of AD users whose City contains the text 'Austin'.
Import-Module ActiveDirectory
Get-ADUser -Filter 'City -like "*Austin*"' -Properties * |
  Select -Property Name,City,Mail,Department,DistinguishedName | 
  Export-CSV C:\\ADUsers.csv -NoTypeInformation -Encoding UTF8
AD PowerShell: Get-ADUser - Export Selected properties to CSV file

Export ADUsers CSV output:

AD PowerShell: Get-ADUser - Export Selected properties to CSV file

Thanks,
Morgan
Software Developer
Read More...

Saturday, 25 October 2014

How to: Run Program or Script as Local System Account

   Running a Program or Batch file under different user account is one of the easiest way to impersonate a Program/Application with some other user's privilege. For every administrators, this would be the common need either for testing or to impersonate different user privilege. In this article, I am going to write and explain about how to run a program or batch script under Local System account context.

You can execute or run a program under local system account by using sysinternals's Psexec utility. you can download at http://technet.microsoft.com/en-us/sysinternals/bb897553. You can use the -s switch to run a program as the system account. The following example shows how to start a cmd.exe session under the system account:
C:\PSTools> PsExec -s cmd.exe

PsExec v2.1 - Execute processes remotely
Copyright (C) 2001-2013 Mark Russinovich
Sysinternals - www.sysinternals.com

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>whoami
nt authority\system

C:\Windows\system32>exit
cmd.exe exited on HP-PC with error code 0.
How to Run Program or Batch file under Local System Account

Once you have completed the work you can revert to current user by executing exit command.


Thanks,
Morgan
Software Developer
Read More...

AD Account Logon, Failure and Lockout Audit Event IDs

Description:

Auditing AD user logon activity is the one of the importance task for every System Admin to ensue AD Domain security. This auditing should includes the user logon, logoff, logon failure and account lockout. Active Directory Logon and Logon failure events are categorized under following three categories

Account Logon/Logon failure Event IDs (Domain Controller events):

When a domain user login into his/her client pc which connected the Active Directory domain, the domain user account is authenticated by a domain controller (logon server) before login into client-pc. At this time, either logon or logon failure will event will be logged in the Domain Controller(logon server). Checkout the article Enable Account Logon Audit Event IDs to configure Group Policy to log account logon audit events in DC.

Event ID Event Type Reason
4768 Success/Failure A Kerberos authentication ticket (TGT) was requested to DC.
4769 Success/Failure A Kerberos service ticket was requested to DC.
4771 Failure Kerberos pre-authentication failed.
4776 Success/Failure The domain controller attempted to validate the credentials for an account.

Account Lockout Event ID: 4740

When a domain user login into his/her client pc which connected the Active Directory domain with wrong password continuously, the account lockout event 4740 will be logged in Domain Controller (logon server). See this article Event 4740 to know more about 4740.

Logon/Logoff events (Client events):

Logon/Logoff Audit events will be logged in local computer, when a user login either by using a domain account or a local account. The logon (4624) and logon failure (4625) event contains the detailed info about user logon activity. Checkout the article Enable Logon/Logoff Audit Event IDs to configure Group Policy to log logon audit events in client-pc.

Event ID Audit Type Event Type Reason
4624 Logon Success An account was successfully logged on.
4625 Logon Failre User account failed to log on.
4634 Logoff Success User account was logged off.
4647 Logoff Success 4647: User initiated logoff.



Thanks,
Morgan
Software Developer
Read More...

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

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