All posts by npulis

Fix: BEGIN TRY does not work with BULK INSERT on SQL

I have created a stored procedure that does a BULK IMPORT. I wanted to add a bit of fault checking and added a BEGIN TRY and a BEGIN CATCH so that it sends an email with the error message if the T-SQL fails. The problem is that the BEGIN CATCH was being skipped and the procedure stops processing with the below error message.

Msg 4860, Level 16, State 1, Line 2
Cannot bulk load. The file "C:\temp\fileimport.txt" does not exist.

To fix the issue what I did is to add the BULK INSERT command in a variable and execute it. This way, the BEGIN CATCH is not ignored or skipped.

Fix below

BEGIN TRY
DECLARE @bulkimport varchar(1000)
SET @bulkimport = 'BULK INSERT mytable FROM ''C:\temp\fileimport.txt''
WITH (FIELDTERMINATOR = ''*'', ROWTERMINATOR = ''\n'')'
EXECUTE (@bulkimport)
END TRY

BEGIN CATCH
SELECT error_message()
END CATCH

(13)

Fix: Windows 10 Home to Pro upgrade error 0x803fa067

I came across an upgrade issue when I was upgrading Windows 10 Home to Windows 10 Pro using the Change Product Key feature. I first entered the default Windows 10 key from Microsoft VK7JG-NPHTM-C97JM-9MPGT-3V66T to convert the Windows 10 Home to Windows 10 Pro and when I clicked the start button to start the upgrade, I get the error saying that the the installation does not have a valid digital license with the error code Unable to upgrade your edition (0x803fa067).

The solution is very simple

– Boot the computer
– Open the activation screen
– Change the Product Key to VK7JG-NPHTM-C97JM-9MPGT-3V66T
– Disable internet connectivity by disabling network card
– Click start for the upgrage

(922)

How to: Execute DOS commands from T-SQL

Sometimes you might have the need to execute a copy, delete or run a batch file from a stored procedure. This can be done by using the xp_cmdshell and execute your command line.

This can be done as below

EXECUTE master..xp_cmdshell 'copy c:\test.txt c:\test1.txt'

You can run it using parameters as below

DECLARE @sql VARCHAR(200)
SET @sql = 'copy c:\test.txt c:\test1.txt'
EXECUTE master..xp_cmdshell @sql

If you are running the xp_cmdshell and get the error "SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server." Follow the code below to enable the feature.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

(9)

Review: Stellar Phoenix Mailbox Exchange Recovery

Recovering data and mailboxes from Microsoft Exchange EDB databases can sometime be a nightmare. I came across the software from Stellar Phoenix called Mailbox Exchange Recovery and for a small software, I managed to solve huge problems while minimising the time of recovery and the time effort to recover the mailboxes/items or EDB databases versus the conventional tools available.

Apart from solving the problems I had, Stellar Phoenix Mailbox Exchange Recovery is a simple to use software. When you open it, you just point the software to the EDB file and it will automatically scan the EDB file to list the mailboxes. Depending on the corruption of the EDB database you can choose between Quick Scan or Extensive Scan. Once the scan is complete, you will get a full preview of all mailboxes in the database with the facility to browse in the actual folders. Apart from that you can also have a preview of the email you are looking at in the right pane. With this software you can also scan and export from multiple EDB files if you have multiple departments and create an EDB for each one.

If you have a specific recovery criteria from the user, you can easily filter by To, From, CC, Subject, Body, Attachment, Importance and Item Type; apart from the date range. There are various export options you ca use which some are PST, MSG, EMAIL. It came very useful when I had to recovery mails or mailboxes from a decommissioned Exchange Server, where I just restored the EDB file and exported all the mailboxes to PST in one go. This version of Stellar Phoenix Mailbox Exchange Recovery can server also in migration projects to Office 365, where for example I found it very useful with Exchange 2007 clients migration to Office 365 and apart from exporting all mailboxes to PST was very easy and fast. On the other hand, you can easily restore the mailboxes to a live EDB file or directly to an Office 365 tenant. Really came in handy for a client who was migrating to Office 365 from a Exchange 2007. The solution was to export all mailboxes to PST, upload them and importing them through Office 365 portal but then this software allowed me to open the EDB file and export/import the mailbox directly to Office 365 without any hassle with all the calendar, tasks ad folder structure the user had in his on-premise solution. So apart from a recovery tool, I found it an excellent tool for migration tasks.

To cut story short, you can go with the repair, scan and use other applications to recover a corrupted EDB, but to be honest why would you end up with a huge amount of time scanning and repairing where you can easily use this software to recover you data and not having raging users waiting to access their precious emails? With Stellar Phoenix Mailbox Exchange Recovery, one can easily create a new EDB file containing new mailboxes, export all mailboxes to PST using the application and restore business in no time.

In conclusion this is a great tool to have if you have Microsoft Exchange servers or if you are in a position to migrate to Office 365. The software is so easy to use and for the price, it’s really worth the money starting from $399 to $999 depending on the license needs. If you are still indecisive, you can always download the trial to check if your EDB file is recoverable. The software supports Exchange 2016 downwards to Exchange 5.5.

Check the software on the link below and get your trial now

https://www.stellarinfo.com/edb-exchange-server-recovery.htm

(49)

How to: Find the installation date of an operating system

Sometimes I would need to find the date when the operating system was installed. Thou it’s difficult to have everything in shape with your inventory, if you have missed this part when an Operating System was installed, here’s a PowerShell solution to that.

Open PowerShell as admin
Type $osdate = get-wmiobject win32_operatingsystem
Type $osdate.ConvertToDateTime($os.InstallDate) -f "MM/dd/yyyy"

This will give you the date when the Operating System was installed

On the other hand, if you wish to go through registry yourself, browse to the location below

HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\InstallDate

The result is in timestamp, so you need to convert it with this link.

(11)

How to: Add AzureAD user as local admin

After joining a computer to AzureAD you will login with a user and will be automatically added as a local admin. The trouble is when logging with other users. These users will automatically set as users to the computer. From the Computer Management Console there is no way to add an AzureAD as when you click on Add and Locations, there is no location for the AzureAD. If you look at the current AAD user in the Computer Management and Local Users and Groups you will find the current user as AZUREAD\noel.pulis.

Luckily there is a way to add an additional AzureAD user as a local admin.

– Open CMD (Command Prompt) as Admin
– Type NET Localgroup Administrators AzureAD\additionaluser

Once this is ready, open the Local Users and Groups and you will find the AzureAD user part of the local Administrators Group.

(61)

How To: Save mail sent as Shared Mailbox in sent items Shared Mailbox

When you have a situation of a user having full access and send as access on a shared mailbox and the user sends an email send-as or on behalf of a Shared Mailbox, the sent item will be saved in the user’s mailbox and not in the Shared Mailbox.

To fix this, there is not option in the GUI so far so you would need to connect via Poweshell as the Global Admin. Note: If you are using Multi-Factor Authentication use the App Password to login instead of the password.

Open PowerShell as Administrator and type the following

$Cred = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $Cred -Authentication Basic -AllowRedirection

Import-PSSession $Session

set-mailbox "<mailboxemailaddress>" -MessageCopyForSentAsEnabled $True

set-mailbox "<mailboxemailaddress>" -MessageCopyForSendOnBehalfEnabled $True

This will still save a copy in the user’s mailbox but will also save it in the Shared Mailbox

(24)

Fix: 550 5.1.8 Access denied, bad outbound sender

I have been having problems with one particular user in Office 365 who could not send or receive emails and he was always getting the error that the email was not delivered due to the below error.

Your message couldn't be delivered because you weren't recognized as a valid sender. The most common reason for this is that your email address is suspected of sending spam and it's no longer allowed to send messages outside of your organization. Contact your email admin for assistance.

Diagnostic information for administrators:
Generating server: --------------.eurprd02.prod.outlook.com
Remote Server returned '550 5.1.8 Access denied, bad outbound sender'

The problem is that the email was being blocked by Microsoft due that 5000 emails have been sent by the mailbox. The problem is not that your mailbox was hacked, but that the email header was spoofed by someone. To check that the mailbox is being blocked, open the Exchange Admin Center in your Office 365 portal, click on Protection and on Action Center.

You will see the user listed there with an unblock. Do not unblock the user for now.

In the Protection screen, click on dkim and highlight your external domain. Click on Enable. You will get an error message that CNAME records required are not found.

 

Open your domain DNS management portal on your hosting company and add the following CNAME entries

Host name: selector1._domainkey.<domain>
Points to : selector1-<domainGUID>._domainkey.<initialDomain>
TTL: 3600

Host name: selector2._domainkey.<domain>
Points to : selector2-<domainGUID>._domainkey.<initialDomain>
TTL: 3600

Once your DNS records have propagated, click on the Enable button on the dkim section.

Once enabled you can go under the Action Center and unblock the user. The process may take up to 2 hours to be cleared.

This will protect you from email message header spoofing. On another note to know immediately if a user has been blocked, you need to setup a notification as below.

Under the Exchange Admin Center open the Protection/ Outbound Spam section. Double click on default. Click on Outbound Spam preferences and tick send a notification when a sender is blocked as below and enter the admin email address. Click Save.

 

(5824)

Fix: No Suitable Directory Servers Found when accessing OWA and ECP

When accessing ECP or OWA on your Exchange server you will be the below Server Error saying that No Suitable Directory Servers Found in Site and connected Sites. On the Event Viewer you will get the error 0x80040a02 (DSC_E_NO_SUITABLE_CDC).

The below solution if for Exchange 2003, 2007, 2010 and 2013. Open the Domain’s Group Policy Management and edit the Default Domain Controllers Policy or your server policy as below.

Computer Configuration
Policies
Windows Settings
Security Settings
Local Policies
User Rights Assignment
Mange auditing and security log
Add ‘Exchange Servers‘ or ‘Exchange Enterprise Servers‘ to that policy.

Restart the Exchange server to apply the computer configuration.

(36)