This discussion is archived
4 Replies Latest reply: Dec 12, 2012 7:57 AM by jstem1177 RSS

EM12c: Add Target Named Instance SQL Server 2008 Fails

Marcelo Marques - ESRI Newbie
Currently Being Moderated
EM12c: Add Target Named Instance SQL Server Fails
---------------------------------------------------
Configuration:

EM12c platform:
Oracle Enterprise Linux 6.2(x86_64)
Oracle Enterprise Database 11.2.0.3 (x86_64)
Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1) (x86_64)
Microsoft SQL Server Plug-In 12.1.0.2.0 deployed in the Management Server

Target:
Windows Server 2008 R2 Enterprise Edition SP1 (64-bit)
SQL Server 2008 R2 SP1 (Enterprise Edition) (64-bit)
Microsoft SQL Server Plug-In 12.1.0.2.0 deployed in the Management Agent
---------------------------------------------------

Problem: EM12c fails to add a SQL Server named instance.

Target Name: mssql_rhsqldb.esri.com_rhsqldb2     
Target Type: Microsoft SQL Server
Agent: https://rhsqldb.esri.com:3872/emd/main/
* JDBC URL (Example : jdbc:sqlserver://<host>:<port>): jdbc:sqlserver://rhsqldb.esri.com\rhsqldb2:1433
JDBC Driver (Optional): com.microsoft.sqlserver.jdbc.SQLServerDriver
Database Username (Required for SQL Authentication): sa
Password of Database User (Required for SQL Authentication): ****     
System Username (Needed when SQLServer is at remote location):
System Password (Needed when SQLServer is at remote location):     
Connect Using Windows Integrated Authentication (Yes/No): No
Role (Optional):

Error:
WbemRemote_Determination_DynamicProperty;Couldn't resolve the value `rhsqldb.esri.com\rhsqldb2` of the SQL Server Host by perl routine call gethostbyname. Possible reasons are:
- Host Name or IPAddress provided in the JDBC URL is not correct.
- Domain Name Server (DNS) is down or Domain Name resolution failed. Please contact your system/network administrator to resolve the DNS configuration.

---------------------------------------------------
http://msdn.microsoft.com/en-us/library/ms378428(v=sql.90).aspx

The general form of the connection URL is

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

where:

jdbc:sqlserver:// (Required) is known as the sub-protocol and is constant.

serverName (Optional) is the address of the server to connect to. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. If not specified in the connection URL, the server name must be specified in the properties collection.


instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made.


portNumber (Optional) is the port to connect to on serverName. The default is 1433. If you are using the default, you do not have to specify the port, nor its preceding ':', in the URL.


Note:
For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

---------------------------------------------------

The sa login is enabled and I can connect from a remote machine to the server using SQL Server Management Studio.

I have also added several default MSSQL 2008 instances to EM12c successfull.

I've installed the latest "Microsoft SQL Server Plug-In 12.1.0.2.0" in EM12c and deployed it to the target agent machine.

I have also tried the following and get the same error.

* JDBC URL (Example : jdbc:sqlserver://<host>:<port>)

jdbc:sqlserver://rhsqldb.esri.com\rhsqldb2:1433
jdbc:sqlserver://rhsqldb.esri.com\rhsqldb2
jdbc:sqlserver://rhsqldb.esri.com;instanceName=rhsqldb2

Am I missing anything?

Thanks,

Marcelo Marques
Technical Manager, Esri

Edited by: Marcelo Marques - ESRI on Sep 25, 2012 4:03 PM
  • 1. Re: EM12c: Add Target Named Instance SQL Server 2008 Fails
    Marcelo Marques - ESRI Newbie
    Currently Being Moderated
    Hi,

    I'm replying to my own thread. I didn't get any replies therefore I opened a SR.

    "SR 3-6254758832 : EM12c: Add Target Named Instance SQL Server 2008 Fails"

    Oracle support is working to reproduce the issue.

    I suspect a named sqlserver instance is not supported in EM12c.

    Thanks,

    Marcelo Marques
    Esri Technical Manager
  • 2. Re: EM12c: Add Target Named Instance SQL Server 2008 Fails
    caroy Journeyer
    Currently Being Moderated
    By default, named instances use a dynamic port. You can try and find the port your named instance is currently running on and use that as the port for your instance (instead of the 1433 default). However, if your instance gets restarted, there's no guarantee you'd get the same port back and you'd have to change your target properties to the new port every time your instance was restarted.

    The other option is to define a static port for the named instance (they support both dynamic and static ports) and then use that port in the target instance properties. You'll want to be careful to choose a port which won't be used by another process. Instructions for setting this up are here:

    http://support.microsoft.com/kb/823938

    Click on the section entitled: Configuring an instance of SQL Server to use a static port
  • 3. Re: EM12c: Add Target Named Instance SQL Server 2008 Fails
    Marcelo Marques - ESRI Newbie
    Currently Being Moderated
    Hi,

    I changed the named mssqlserver instance to listen in a static port 1435 instead of the dynamic port and now I was able to add the target in EM12c.

    Thanks,

    Marcelo Marques
    Esri, Technical Manager
  • 4. Re: EM12c: Add Target Named Instance SQL Server 2008 Fails
    jstem1177 Explorer
    Currently Being Moderated
    Hello Marcelo,

    Just wanted to add that the link you provided forces a change directly in the regsitry. Better option would be to use:
    Click on Start button in Windows. Go to All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager
    Click on SQL Server Network Configuration -> Protocols for [SQL Server Instance Name] -> TCP/IP double click ( Right click select Properties ) on TCP/IP.
    *!! Also do not forget to make the PORT change in the Windows Firewall for the service (in case you have your firewall turned on).*

    Nonetheless a very helpfull thread for the Non MSSQL people.

    Thanks

    Jan S.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points