How to identify what features has been installed in your SQL Server

How to view the list of features installed in the SQL Server easily without going through “Programs and Features” or “Services”.


Yes, there is an easy way.

Method 1: 

we can navigate to the Following Path and see the summary file.

SQL server 2012 Features Installation Discovery.

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt

SQL Server 2008 Features Installation Discovery.

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

Method 2:

Second method is you can go to the Startup and choose the SQL Server Installation Center.

Select Tools on the Left side  and choose Installed SQL Server Features Discovery Report

you will get the Report in internet explorer.

Regards

R.Karthikeyan

Ports for windows file sharing

Sharing a folder or file creates a Windows Firewall exception for File and Printer Sharing. The exception opens the ports listed in the following table.

"If the server has NBT enabled, it listens on UDP ports 137, 138, and on TCP ports 139, 445. If it has NBT disabled, it listens on TCP port 445 only."

Connection	Ports
TCP	139, 445
UDP	137, 138

The default scope is to allow access from any computer on the network, including computers on the Internet. Unless you block incoming connections on these ports using a hardware firewall, firewall server, or other Internet-sharing device, your computer will be vulnerable to attack from the Internet as long as your Internet connection is active.

Regards 
கார்த்திகேயன் 
Karthikeyan 

useful commands-Fortinet Firewall-Part 2

Viewing and clearing a DHCP lease is a function you may need to often perform. 

The DHCP option is located in different areas of the GUI depending on the firmware version you are running.

The below commands allows you to view and clear a DHCP lease through the CLI.

Commands

exec dhcp lease-list –> show current list on DHCP lease
execute dhcp lease-clear <ip_addres> –> clear the DHCP lease of a specific ip
execute dhcp lease-clear all —> clear all the DHCP leases

Regards

R.Karthikeyan

The database principal owns a schema in the database, and cannot be dropped

Last day I had faced issue on SQL Server Login. I was trying to remove the login from database but every single time I was getting error and was not able to remove the user.

How to resolve following error.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

I was searching on google and find the solution. Here is the quick workaround to the issue. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Workaround / Resolution / Fix:

Let us assume that i was trying to delete user which is named as ‘tester’ and it exists in the database ‘Testdb’.

Now run following script with the context of the database where user belongs.

USE Testdb;
SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('tester');

In my query I get following two schema as a result.

two schema name "tqd" and "cls" 

Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::cls TO dbo;

ALTER AUTHORIZATION ON SCHEMA::tqd TO dbo;

Now if you drop the database owner it will not throw any error.

Here is generic script for resolving the error:

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('youruserid');

 Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaNa TO dbo;

Thanks to pinal dave for such a good article.

Regards

R.Karthikeyan

Fortigate Firmware upgrade Process On HA

Recommend before the upgrade:
1) Check the release notes for supported upgrade path, special notices, product integration, known issues and limitations if any.
2) Backup configuration before and after each upgrade.
3) Plan a maintenance window for the upgrade.
3) Have some one available on the remote site in case something went wrong during the upgrade.
4) Make sure check sum is matching between cluster members using the following CLI commands:
# get sys ha status 
# diag sys ha showcsum
# execute ha manage <Slave ID>   <<-- could be 0 or 1, check "get sys ha status" results
$ diag sys ha showcsum
$ exit
 
The results of "diag sys ha showcsum" should be the same on all levels (all/global/vdoms)
 

Windows Time Service Issue

I just wanted to make a statement regarding the time service registry entries. There really is no need to modify the time service registry entries. The time service works by default, out of the box. The only thing that’s recommended to do, is synchronize the PDC Emulator in the forest root domain to a reliable outside source. That’s it.

I’m stating this because based on numerous public postings regarding corrupted time service settings due to attempts at changing registry entries because it was thought that’s how it’s done, is usually the culprit that corrupted the time service settings. The time service should only be configured using the w32tm utility.

If there are any problems with corrupted settings, and it’s not working properly, I would suggest to simply reset the time service itself (stated in the “To Reset the Time service” section below), by simply running the following commands:

If you’ve experimented changing time settings to unknowlingly avert default behavior, you can set the time settings back to default:

1. On the DC that you’re experiencing issues with, run the following in a command prompt:

•  net stop w32time
•  w32tm /unregister
•  w32tm /register
•  net start w32time

2. On the Server in question (whether it’s the PDC Emulator or another server), run the following in a command prompt: 

• “net time /setsntp: ” (Note the blank space prior to the end “)  [This tells the client (whether a DC or workstation) to delete the current registry settings for time and use default settings.]
• Restart the time service:  Net stop w32time && net start w32time

3. On the PDC Emulator run the following in a command prompt:

• W32tm /config /manualpeerlist:time.nrc.ca /syncfromflags:manual /reliable:yes /update
•  W32tm /resync /rediscover
• Restart the time service: net stop w32time && net start w32time

4. On each DC that are not holding the PDC Emulator role, run the following in a command prompt:

• w32tm /config /syncfromflags:domhier /update
•  W32tm /resync /rediscover
• Restart the time service: net stop w32time && net start w32time

5. This will take out any errors in the Event Viewer, if there are any.

.The only real time that you may have to configure it is only with the assistance of Microsoft Support.

Boot from ISO image failed on Generation 2 VM in Hyper V

Generation 2 virtual machines in Hyper-V talks booting from CD/DVD. It’s not specific to Hyper-V, it’s more about PCAT and EFI, and the way in which Windows installation media is built.

Try the following – create a new generation 1 and generation 2 VMs with blank VHDXs, setting them both to boot from Windows 8 64-bit .ISO media, and start them.

Here’s what you’ll see in a generation 1 virtual machine (or PCAT physical system)

And here’s what you’ll see in a generation 2 virtual machine (or EFI physical system)

Before anyone comments, yes, Microsoft acutely aware that the generation 2 EFI boot loader messages persist on the screen and it can be a little confusing. Essentially what the generation 2 virtual machine did was:

• Attempt to boot from the CD SCSI device containing the Windows 8 ISO. This is where the ‘Press any key…’ message came from. As no key was pressed, we went to the next boot entry. (And this is where we didn’t clear the message from the EFI CD boot loader)
• I didn’t press F12 (in fact, this VM wasn’t connected to a network), so network boot failed
• The SCSI VHDX is raw with no partitioning or file system, so this too failed

So why did the generation 1 virtual machine start setup from the Windows media? The answer is simply that it’s the way Windows media is built, and it’s inconsistent between the PCAT and EFI loaders.

However, it’s relatively simple to solve if you want to avoid the press any key message. In fact, we almost have all the pieces from previous parts. In particular, part 4 where we injected keyboard drivers into the Windows 8 media.

If you loopback mount Windows 8 or 8.1 RTM media, and navigate to the \efi\microsoft\boot directory, you will see there are two versions of cdboot.efi

The default version, cdboot.efi prompts for a keypress. The unused version, cdboot_noprompt.efi doesn’t prompt.

So it’s fairly simple to create modified media which uses the noprompt version. First, copy the contents of the ISO to a working directory, rename cdboot.efi to cdboot_prompt.efi, and rename cdboot_noprompt.efi to cdboot.efi.

Do the same with efisys.bin and efisys_prompt.bin

Then re-use our oscdimg command (in an elevated deployment and imaging tools environment) from part 4 to recreate the media.

you can download oscdimg from below Link

https://drive.google.com/open?id=0Bz5RYxg0eeodbUxQRW1kVndSTzg
or
http://api.256file.com/download/87465_oscdimg.exe

if you receive ERROR: With -u2, cannot use -n, -nt, -d, -j1, -j2, or -oi

just  skip -n

eg:-

 d:\Software>oscdimg  -o -u2 -udfver102 -bootdata:2#p0,e,b"D:\Software\win2012nop
rompt\boot\etfsboot.com"#pEF,e,b"D:\Software\win2012noprompt\efi\microsoft\boot\
efisys.bin" "D:\Software\win2012noprompt" C:\Working\WIN2012noprompt.iso

Simply attach this modified media to the generation 2 VM and restart it (and don’t press any keys, not that you will be prompted.

Regards

R.Karthikeyan

Find the ErrorLog path in SQL Server 2012 through Query

One other way, that we could get the path of the ErrorLog  in SQL Server 2012,when the SQL Server service is running  is through this DMV.  we can find out the Error Log Path.

SELECT is_enabled,[path],max_size,max_files

FROM

Sys.dm_os_server_diagnostics_log_configurations

Executing this DMV will give valuable information about the ErrorLog  like

•  Path indicating the location of the diagnostic logs.
•  Maximum size in megabytes to which each of the diagnostic logs can grow.
•  Maximum number of diagnostic log files that can be stored on the computer before they are recycled for new diagnostic logs.
•  If logging is enabled or not.

Regards

R.Karthikeyan

How to redirect http to https in IIS

1) Download and install the URL Rewrite 2.0 module to your IIS8 server:

Download from here: http://www.iis.net/downloads/microsoft/url-rewrite

2) Once installed, open IIS Manager, expand the Sites container and select the website you wish to configure the http redirection.

3) Once highlighted, double click the URL Rewrite option in the right hand pane. Select Add Rule and configure the following:

NAME

Select: Blank Rule

Name: http to https

Match URL – Requested URL: Matches the Pattern

Match URL – Using: Regular Expression

Pattern: (.*)

CONDITIONS

Conditions: Add

Condition Input: {HTTPS}

Check if input string: Matches the Pattern

Pattern: ^OFF$

Click OK

ACTION

Action type: Redirect

Redirect URL: https://{HTTP_HOST}/{R:1}

Redirect type: See Other (303)

Click Apply

Requests to your site via HTTP should now be redirecting to HTTPS

How to change IPAddress of HP ILO, blades and interconnect modules through OA.

Recently I was working on changing HP c7000 enclosure on-board administrator (OA), individual blades and interconnect modules ILO and MSA 1040 Storage Controllers IPs . It’s straight forward but I would like to share my experience here.

 Before doing anything further in production/ working environment, take the backup of Virtual Connect (Flexfabric).

Notes:

1.            From networking point of view, all IPs are going to be in same subnet. Here in this scenario, 

2.            Version - OA 4.30
( If you are looking change blade name in HP On-board Administrator, refer to "Steps to change blade in on-board administrator)

Step1: Change OA IP.

•             Login to HP OA

•             Verify OA fail over to make sure both OA’s are still working after initial implementation ;-). I know our friendly BAU team hardly tests/ patch the environment

•             One more point to note, when you are doing OA failover test, it’s not necessarily your active OA IP will go onto passive OA IP based on below setting

•              Navigate to enclosure TCP/IP settings

•             Change standby on-board administrator ip to new one

•             Connect network cable to newly allocated port

•             Make sure that able to ping new IP

•             Failover OA to standby

•             Change active OA IP to new one

•             Connect network cable to newly allocated port

•             Make sure that able to ping new IP

•             Fail back OA to its actual active OA

Step 2: Change individual blade IP

•             Login to HP OA.

•             Navigate to Enclosure Bay IP Addressing

•             Change Device bay IP address to new ones according as allocated

•             Make sure that able to ping new IPs

Steps 3: Change interconnect module IP (Virtual/ flex connects)

•             Login to HP OA

•             Navigate to enclosure TCP/IP settings- Interconnect bays

•             Change Device bay 1, 2, 3 and 4 IPs to new as pre allocated.( Note : Device bay depends on how many modules you have)

•             Make sure that able to ping new IPs

Update respective DNS alias with new names for all the above devices.

Please share on social media if you found this post helpful. If you have a comment or question, please post and add your voice to the conversation.

Regards

R.Karthikeyan