SQL Server - Use specific IP/Ports not all

Hi All,

Just something that threw me this morning and took a while to understand what was going on.

note: to disable dynamic ports, just delete the value in the field. To reenable just add 0 and restart the sql instance.

If you need\want your SQL server instance to only listen on specific IPs (and not all IPs in the machine), then you must set “Listen All” on the protocol tab to “No”, whilst this seems intuitive now there is an IP All section on the IP Addresses tab, that when you look at articles on the interwebs, this is the setting you change.

IPAll section on the IP Addresses tab

Then in the IP section you want to apply to, set the Enabled setting to Yes, restart the SQL server instance and the server should now be listening on only the server IP.

This does not affect the listeners which are on different IPs, and handled by the cluster and alwayson services.

SQL Server - Use specific IP/Ports not all

SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Had an issue where I was unable to get the “New Availability Group” wizard to successfully complete.

It would continually error with a 9692 Error, indicating that the endpoint couldn't be setup on port 5022.

I had expected this and on the endpoint tab I had changed to a different port (the servers already had other instances running on port 5022), however even though I changed the wizard to an unused port (i.e 5025), the error kept coming back with could not create end point on port 5022.

I had not come across this before and I cannot find anything about it on the interwebs..

The way I got around this was to manually setup the endpoints, before running the wizard (Changing the port to the desired value).

-- Create a new Database Mirroring Endpoint on Replica 1
CREATE ENDPOINT Hadr_Endpoint
AS TCP
(
   LISTENER_PORT = 5023
)
FOR DATA_MIRRORING
(
   ROLE = ALL,
   ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

-- Start the Endpoint on Replica 1
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED

DROP ENDPOINT Hadr_Endpoint

When the wizard got to the endpoint it then listed these endpoints and I was able to continue through (the ports are greyed out and can no longer be changed)

Would love to know why on this occasion the wizard failed, as I have happily changed the port in the past but this work around got me through.

SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect. Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.

servername\instancename

Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

“The target principal name is incorrect.  Cannot generate SSPI context. “

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue

 

 

References

https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

https://support.microsoft.com/en-in/help/2985455/kerberos-configuration-manager-for-sql-server-is-available

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

https://social.technet.microsoft.com/Forums/systemcenter/en-US/b879b1a7-c996-4a3f-8e9d-a70ebdffca44/spns-for-named-sql-instances?forum=operationsmanagergeneral

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65bcf7a3-ae42-43ae-958e-11dccb123e80/setspn-kerberos-and-named-instances?forum=sqlsecurity

https://social.technet.microsoft.com/wiki/contents/articles/18996.active-directory-powershell-script-to-list-all-spns-used.aspx

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!

AzureRM - Powershell Notes

Run POWERSHELL as admin

Get Verison of PowerShellGet

Get-Module PowerShellGet -list | Select-Object Name,Version,Path

What is PowerShellGet
https://blogs.msdn.microsoft.com/mvpawardprogram/2014/10/06/package-management-for-powershell-modules-with-powershellget/

I had to use –allowclobber to force the install.

Install-Module AzureRM (–allowclobber)

Load the module

Import-Module AzureRM

Login to azure

Login-AzureRmAccount

Get-AzureRmSubscription

Set-AzureRmContext -SubscriptionName "Example Subscription Two"

New-AzureRmResourceGroup -Name TestRG1 -Location "South Central US"

Get-AzureRmResourceGroup -ResourceGroupName TestRG1

Get-AzureRmResourceGroup
(returns all ResourceGroups)

References:

https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-3.8.0

https://docs.microsoft.com/en-us/powershell/azure/overview?view=azurermps-3.8.0

https://docs.microsoft.com/en-us/azure/azure-resource-manager/powershell-azure-resource-manager

AzureRM - Powershell Notes

Setting up DNS on openwrt on TL-WDR4300

Found a couple of articles that helped, referenced below. Had to connect to router via SSH, as luci does not have the interface for the added packages.

Note: I was unable to install the dhcp server  in the article, although I did manage to install another dhcp package. This said I do not use dhcp and I didnot test whether dhcp worked.

This did not install

opkg install isc-dhcp-server-ipv4

I was able to install the following package from the openwrt site.

opkg install https://downloads.openwrt.org/chaos_calmer/15.05/ar71xx/generic/packages/packages/dhcpcd_6.4.3-1_ar71xx.ipk

Note: I have been running this for months now, and I have just had a weird issue where dns stopped working. SSHing onto the router, and performing a dig worked fine just remote dns was not working. I tried rebooting the router and that didnot fix it. However I just restarted the bind service and all has started working again. Not sure what the actual problem was.

restart the dns service: /etc/init.d/named restart/etc/init.d/named restart

We remove the dnsmasq service which is a combined DHCP and DNS (forwarder) server and replace this with separate DNS and DHCP services.

I then followed the article

https://maroskukan.wordpress.com/2015/02/24/openwrt-spinning-up-authoritative-dns-server/

Changing values where needed (examples below)

I used the following acl in the named.conf file, note two ip ranges as my router is setup as a bridging client.

acl "trusted" {
     192.168.75.0/24;
     172.75.75.0/24;
     localhost;
     localnets;
};

options {
        directory "/tmp";
        recursion yes;
        allow-recursion { trusted; };
        allow-transfer { trusted; };
        allow-query-cache { trusted; };
        // If your ISP provided one or more IP addresses for stable
        // nameservers, you probably want to use them as forwarders.
        // Uncomment the following block, and insert the addresses replacing
        // the all-0's placeholder.

        forwarders {
                192.168.75.1;
        };

        auth-nxdomain no;    # conform to RFC1035
};

Created folder to hold the zone files

mkdir /etc/zones

using existing zone files as templates.

cp /etc/bind/db.local /etc/bind/zones/db.homedomain.local

for reverse lookup (two zones as I have created two subnets)

cp /etc/bind/db.127 /etc/bind/zones/db.75.168.192

cp /etc/bind/db.127 /etc/bind/zones/db.75.75.172

Then edited the files for my needs

;
; BIND data file for reverse look up of 192.168.75/24
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@      IN      NS      router.homedomain.local.
11     IN      PTR     machine1.homedomain.local.
18     IN      PTR     router.homedomain.local.
110    IN      PTR     machine3.homedomain.local.

;
; BIND data file for reverse look up of 172.75.75/24
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@       IN      NS      router.homedomain.local.
1       IN      PTR     router.homedomain.local.
2       IN      PTR     machine4.homedomain.local.
10      IN      PTR     machine5.homedomain.local.

;
; BIND data file for homedomain.local zone
;
$TTL    604800
@       IN      SOA     homedomain.local. root.homedomain.local. (
                             10         ; Serial
                         604800         ; Refresh
                          86400         ; Retry
                        2419200         ; Expire
                         604800 )       ; Negative Cache TTL
;
@       IN      NS      router.homedomain.local.
@       IN      A       192.168.75.18
router  IN      A       192.168.75.18
machine4        IN      A       172.75.75.2
machine5        IN      A       172.75.75.10
machine3        IN      A       192.168.75.110
machine1        IN      A       192.168.75.11

https://wiki.openwrt.org/doc/howto/dns.bind

https://maroskukan.wordpress.com/2015/02/24/openwrt-spinning-up-authoritative-dns-server/

http://blog.differentpla.net/blog/2013/10/30/replacing-dnsmasq-on-openwrt

https://kb.isc.org/article/AA-00269/0/What-has-changed-in-the-behavior-of-allow-recursion-and-allow-query-cache.html

Setting up DNS on openwrt on TL-WDR4300

PowerShell: List domain users with specific homedirectory folder

Recently had to get a list of users within active directory that were pointing at a specific file servers.

Came up with the following PowerShell, searching a specific domain and then looks for servers

Note:

• The \">\">\\*<servername>\*, is because I found that the command had a issue with certain servernames if I just put \">\">\\<servername>\*. I spent a little time looking at this but couldnot find a reason and by slipping in \\* just made it work consistently.

-SearchBase "DC=<domain>,DC=local" 

with your domain… i.e. if your fqdn is company.local then this would read

-SearchBase "DC=company,DC=local"

• Also replace <servernameX> with your server names with your domain… i.e. if your fqdn is company.local then this would read

 

Get-ADUser -Filter {(homedirectory -like '\\*<servername1>\*' -or homedirectory -like '\\*<servername2>\*') -and (Enabled -eq "True")}  -SearchBase "DC=<domain>,DC=local" -Properties homedirectory | Select-Object name, enabled, homedirectory

PowerShell: List domain users with specific homedirectory folder

Importing Keepass to Lastpass

I have been using keepass for years and its been great, but I want to move to an easier to maintain solution, and so I bit the bullet and tried to move to lastpass.

To export and import into last pass is very easy, there were a couple of things that caught me out.

Initially I just did one big export from keepass, however this then put all the folders in lastpass into one root folder. This was not what I wanted. So easiest thing I found was to export individually each folder (at the root level in keepass), into an xml file and then import each xml file into last pass.

The next issue, was how lastpass reads the xml export and makes decisions on what type of record to create. I had a number of entries in key pass without a URL field. Either I hadn't bothered filling it in or it was a password without a URL. Now when these entries (without URL) get imported into lastpass, they get converted into a secure note item... This means the password is in free text on the page. I didnot want this.

So I needed to change the URL to have some sort of content. So using Notepad++ I created a search string and replaced the blank URL fields with dummy content.

<string>
  <key>URL</key>
  <value />
</string>

to

<string>
  <key>URL</key>
  <value>http://</value>
</string>

Saving this change and then importing to lastpass created all entries as sites, and the password is now hidden on the page.

The search strings in notepad++ was (note had to take into account the number of tabs... I suppose I could have come up with a reular expression to do this but that would have taken time and I am not a regular expression guru.... :)

6 tabs

<key>URL</key>\r\n\t\t\t\t\t\t<value />

and the replacement string was

<key>URL</key>\r\n\t\t\t\t\t\t<value>http://</value>

and

7 tabs

<key>URL</key>\r\n\t\t\t\t\t\t\t<value />

and the replacement string was

<key>URL</key>\r\n\t\t\t\t\t\t\t<value>http://</value>

The search string contains the carridge return and new line feed (\r\n) and then the tabs (\t).

Importing Keepass to Lastpass