Advertisements

Archive

Archive for the ‘SQL Errors’ Category

SQL Error – Cannot connect to xyz_sql_instance, due to remote firewall issue

June 11, 2017 Leave a comment

 
After creating a new Azure Windows Server VM with SQL Server installed, I was not able to connect it from my PC via SSMS. Everytime I tried to connect I was getting following error:

Cannot connect to xyz_sql_instance.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provier, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
The network path was not found


 

–> I checked online and found following command to enable the port, but it ran into error:

C:\windows\system32>netsh firewall set portopening TCP 1433 “SQLServer”

“… netsh firewall” is deprecated; use “netsh advfirewall firewall” instead.


 

–> So, lets execute the new netsh advfirewall firewall command and see what options it has:

C:\windows\system32>netsh advfirewall firewall /?

The following commands are available:

Commands in this context:
? – Displays a list of commands.
add – Adds a new inbound or outbound firewall rule.
delete – Deletes all matching firewall rules.
dump – Displays a configuration script.
help – Displays a list of commands.
set – Sets new values for properties of a existing rule.
show – Displays a specified firewall rule.

To view help for a command, type the command, followed by a space, and then type ?.

 

–> Now with above options we are clear that we have to add a new inbound firewall rule, so we will check how can we use the add option:

C:\windows\system32>netsh advfirewall firewall add /?

The following commands are available:

Commands in this context:
add rule – Adds a new inbound or outbound firewall rule.

 

–> Let’s apply the add rule option and see more options:

C:\windows\system32>netsh advfirewall firewall add rule /?

Usage: add rule name=
dir=in|out
action=allow|block|bypass
[program=]
[service=|any]
[description=]
[enable=yes|no (default=yes)]
[profile=public|private|domain|any[,…]]
[localip=any|||||]
[remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
||||]
[localport=0-65535|[,…]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
[remoteport=0-65535|[,…]|any (default=any)]
[protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
tcp|udp|any (default=any)]
[interfacetype=wireless|lan|ras|any]
[rmtcomputergrp=]
[rmtusrgrp=]
[edge=yes|deferapp|deferuser|no (default=no)]
[security=authenticate|authenc|authdynenc|authnoencap|notrequired
(default=notrequired)]

Remarks:

– Add a new inbound or outbound rule to the firewall policy.
– Rule name should be unique and cannot be “all”.
– If a remote computer or user group is specified, security must be
authenticate, authenc, authdynenc, or authnoencap.
– Setting security to authdynenc allows systems to dynamically
negotiate the use of encryption for traffic that matches
a given Windows Firewall rule. Encryption is negotiated based on
existing connection security rule properties. This option
enables the ability of a machine to accept the first TCP
or UDP packet of an inbound IPsec connection as long as
it is secured, but not encrypted, using IPsec.
Once the first packet is processed, the server will
re-negotiate the connection and upgrade it so that
all subsequent communications are fully encrypted.
– If action=bypass, the remote computer group must be specified when dir=in.
– If service=any, the rule applies only to services.
– ICMP type or code can be “any”.
– Edge can only be specified for inbound rules.
– AuthEnc and authnoencap cannot be used together.
– Authdynenc is valid only when dir=in.
– When authnoencap is set, the security=authenticate option becomes an
optional parameter.

Examples:

Add an inbound rule with no encapsulation security for browser.exe:
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\programfiles\browser\browser.exe”
security=authnoencap action=allow

Add an outbound rule for port 80:
netsh advfirewall firewall add rule name=”allow80″
protocol=TCP dir=out localport=80 action=block

Add an inbound rule requiring security and encryption
for TCP port 80 traffic:
netsh advfirewall firewall add rule
name=”Require Encryption for Inbound TCP/80″
protocol=TCP dir=in localport=80 security=authdynenc
action=allow

Add an inbound rule for browser.exe and require security
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\program files\browser\browser.exe”
security=authenticate action=allow

Add an authenticated firewall bypass rule for group
acmedomain\scanners identified by a SDDL string:
netsh advfirewall firewall add rule name=”allow scanners”
dir=in rmtcomputergrp= action=bypass
security=authenticate

Add an outbound allow rule for local ports 5000-5010 for udp-
Add rule name=”Allow port range” dir=out protocol=udp localport=5000-5010 action=allow

 

–> OK, so this gives us more options and also samples at the end to construct our command. So we will us the above highlighted command to add port for our SQL Server, as shown below:

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Engine” protocol=TCP dir=in localport=1433 action=allow

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Browser” protocol=UDP dir=in localport=1434 action=allow
 

Now, I am able to connect remotely without any issues.


Advertisements
Categories: SQL Errors

SSMS error – The Visual Studio component cache is out of date. Please restart Visual Studio.

June 10, 2017 13 comments

 
Yesterday while opening SSMS I got an error message popup which mentions:

The Visual Studio component cache is out of date. Please restart Visual Studio.


 

On checking in internet I found a similar issue logged in MS Connect, and the workaround was to cleanup the application folder in the Windows-Temp folder, which is located at following folder location:

C:\Users\your_user_name_here\AppData\Local\Temp\

Here is the shortcut to go to the TEMP folder:


 

This issue vanished after deleting the SSMS folder. This could be due to the Visual Studio component cache can’t be written or can’t be wiped while in use.


Categories: SQL Errors

SQL Server 2016 Install Error – Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase” failed

February 9, 2017 2 comments

 
While installing SQL Server 2016/2017 with Polybase feature, you might have noticed this issue, and some of you would have no idea what to do next.

—————————
Rule Check Result
—————————
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from https://go.microsoft.com/fwlink/?LinkId=526030.
—————————
OK
—————————

 
–> This is because the new Polybase feature in SQL Server requires Java Runtimes or JRE.
 

1. You are installing SQL Server and you chose Polybase feature.

2. On the next Feature Rule page you get stuck with following error: Oracle JRE 7 Update 51 or higher is required, Failed.

3. You click on the Failed link it popped up an error box giving you more error details shown below:


.
4. You just need to click on the URL provided in the error popup box, or you can directly download it from [here].

5. You will be redirected to the Oracle JRE download page.
– Here you first need to Register/Login
– Accept the License Agreement
– Finally download the JRE EXE file.

6. After JRE installation is completed, just Re-run the Rules and it will be Passed this time, click Next.
 

–> You can also check this in video here:


SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’

December 25, 2016 Leave a comment

 
Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:

Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

 

The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.

–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:

sp_configure 'show advanced options', 1
GO
EXEC sp_configure
GO

adhocdistributedqueries
 

–> To enable it run the below Query:

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC sp_configure
GO

adhocdistributedqueries-set

…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.


SQL Error – The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

December 24, 2016 2 comments

 
I was trying to export a SQL table to Excel and I got below error:

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
——————————

 

sqltoexcel-error
 

After searching a bit I came to know that the above provider is not installed, and I need to install the Microsoft Access Database Engine setup, to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010” (*.mdb and *.accdb) files and “Microsoft Office Excel 2010” (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.

–> Get the Microsoft Access Database Engine 2010 Redistributable, [link].

There will be two files:

1. AccessDatabaseEngine (for. 32-bit)

2. AccessDatabaseEngine_x64 i.e. 64-bit

If your SQL Server is 32 bit, then install just the first one.
But if its 64 bit, then install both, first 32 bit & then 64 bit.


Categories: Excel, SQL Errors Tags: