Link to home
Start Free TrialLog in
Avatar of metalfubar
metalfubar

asked on

Connect Sql 2012 through RDS 2012 needed

Trying to connect through rds box to sql environment.  the front end works on the sql2012 and maps using access as a front end.  The machines are both of the domain and are using windows authentication.

Odbc works locally on the sql box but not through the rds 2012.  What do i need on the rds box to complete the connection?  Full sql version pointing to the sql 2012 instance?  Just sql manager?

I should also mention that this was converted from access db originally as well.

ps.  also want to have one login for users if possible?

thanks,  

metalfubar
Avatar of Mohammed Khawaja
Mohammed Khawaja
Flag of Canada image

Does your application need ODBC connection on the RDS server, if so, have you created the ODBC and tested.  If tests are successful but RDS does not see SQL then it is most likely your RDS server is 64-bit and your application is 32-bit.  In this case, you need to create yourself 32-bit ODBC connections.  To do so, run c:\windows\syswow64\odbcad32.exe.

If problem still persists then ensure to enable TCP/IP protocol for your SQL server by running SQL Configuration Manager, Protocols and also try starting SQL Browser service on the SQL server.
Avatar of metalfubar
metalfubar

ASKER

thanks for your response.

The ODBC connection does not work on RDS to the SQL box using either 32 or 64 bit.  Do I need to install sql server  or sql manager on the rds box to connect to the sql instance?

sql locally works from the access 2007 program.  The tcpip, confg manger and browser service are all working on locally on sql box.  

The question is:  what do I need on the rds box to make the odbc connection?
do i need sql server installed?  Please help!
SOLUTION
Avatar of Mohammed Khawaja
Mohammed Khawaja
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
on sqlbox i have tcp 1444 and udp 1443 enabled on inbound.

on rds i have the same ones enabled on outbound.

do i need any other items?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for the quick response.

 I added these ports and bounced the sql box and both windows fws and still no joy.

this is the error when testing the connection:

Microsoft SQL Server ODBC Driver Version 06.03.9600

Running connectivity tests...

Attempting connection
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

TESTS FAILED!
can you ping the SQL server from the RDS server?

Can you explain actually what are you trying to do?

Are you trying to connect to a network using remote desk and try to connect to SQL? I am a bit confused.
sorry for the delays.  yes both machines are cloud servers.  ips:  192.168.200.x and 192.168.200.y

we have an access 2007 front end connected to an access converted database sql 2012.

we are able to use the sql ok locally but not from the rds box.  they are both not on domains and using windows authentication.  so multiple users would connect through the rds box with local passwords.
can anyone help me out there?
The correct ports are 1434/udp (for SQL Browser Service) and 1433/tcp (for default instance). If your MSSQL is a  named instance, the current port needs to be used instead, and that port might be dynamic. In that case you should open an application based firewall rule allowing all traffic instead of a port-based one.
i changed these and no effect.  

should i use connection to sql server to obtain default settings and use windows nt auth for the connection?

If so then errors, state:  01000, server: 10060 and state:  08001, server 17.

SQL was initial setup for windows auth but should we check these?

name or dns error?
I'm turning firewall off for testing.
the same issue occurs.
Could you provide the output for the following commands:

net start
netstat -b
RDS box:

These Windows services are started:

   Application Information
   Background Intelligent Transfer Service
   Background Tasks Infrastructure Service
   Base Filtering Engine
   Certificate Propagation
   CNG Key Isolation
   COM+ Event System
   Credential Manager
   Cryptographic Services
   DCOM Server Process Launcher
   DHCP Client
   Diagnostic Policy Service
   Diagnostic System Host
   Diagnostics Tracking Service
   Distributed Link Tracking Client
   Distributed Transaction Coordinator
   DNS Client
   Group Policy Client
   IKE and AuthIP IPsec Keying Modules
   IP Helper
   IPsec Policy Agent
   LMIGuardianSvc
   Local Session Manager
   LogMeIn
   LogMeIn Maintenance Service
   MozyPro Backup Service
   Network Connections
   Network List Service
   Network Location Awareness
   Network Store Interface Service
   Plug and Play
   Power
   Print Spooler
   Remote Desktop Configuration
   Remote Desktop Licensing
   Remote Desktop Services
   Remote Desktop Services UserMode Port Redirector
   Remote Procedure Call (RPC)
   RPC Endpoint Mapper
   Security Accounts Manager
   Server
   Shell Hardware Detection
   Superfetch
   System Event Notification Service
   System Events Broker
   Task Scheduler
   TCP/IP NetBIOS Helper
   Themes
   User Access Logging Service
   User Profile Service
   VMware Tools
   Windows Connection Manager
   Windows Event Log
   Windows Font Cache Service
   Windows Management Instrumentation
   Windows Remote Management (WS-Management)
   WinHTTP Web Proxy Auto-Discovery Service
   Workstation

The command completed successfully.

netstat:

i had to run as administrator:

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    127.0.0.1:2002         CDI-RDS-2K12:59189     ESTABLISHED
  TCP    127.0.0.1:59189        CDI-RDS-2K12:2002      ESTABLISHED
  TCP    192.168.200.10:50866   triton:https           CLOSE_WAIT
  TCP    192.168.200.10:59188   64.94.18.153:https     ESTABLISHED





SQL box:

These Windows services are started:

   Application Information
   Background Tasks Infrastructure Service
   Base Filtering Engine
   Certificate Propagation
   COM+ Event System
   Credential Manager
   Cryptographic Services
   DCOM Server Process Launcher
   DHCP Client
   Diagnostic Policy Service
   Diagnostics Tracking Service
   Distributed Link Tracking Client
   Distributed Transaction Coordinator
   DNS Client
   Group Policy Client
   IKE and AuthIP IPsec Keying Modules
   IP Helper
   IPsec Policy Agent
   LMIGuardianSvc
   Local Session Manager
   LogMeIn
   LogMeIn Maintenance Service
   MozyPro Backup Service
   Network Connections
   Network List Service
   Network Location Awareness
   Network Store Interface Service
   Plug and Play
   Power
   Print Spooler
   Remote Desktop Configuration
   Remote Desktop Services
   Remote Desktop Services UserMode Port Redirec
   Remote Procedure Call (RPC)
   RPC Endpoint Mapper
   Security Accounts Manager
   Server
   Shell Hardware Detection
   Software Protection
   SQL Full-text Filter Daemon Launcher (CDI_NET
   SQL Server (CDI_NETWORK)
   SQL Server Analysis Services (CDI_NETWORK)
   SQL Server Browser
   SQL Server Reporting Services (CDI_NETWORK)
   SQL Server VSS Writer
   System Event Notification Service
   System Events Broker
   Task Scheduler
   TCP/IP NetBIOS Helper
   Themes
   User Access Logging Service
   User Profile Service
   VMware Tools
   Windows Connection Manager
   Windows Event Log
   Windows Font Cache Service
   Windows Management Instrumentation
   Windows Remote Management (WS-Management)
   WinHTTP Web Proxy Auto-Discovery Service
   Workstation

The command completed successfully.

netstat:

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    127.0.0.1:2002         CDI-SQL-2K12:53498     ESTABLISHED
  TCP    127.0.0.1:53498        CDI-SQL-2K12:2002      ESTABLISHED
  TCP    192.168.200.11:53497   app01-07:https         ESTABLISHED
OK...to connect to the SQL, using CDI-SQL-2K12\CDI_NETWORK for the SQL server name and try again.
thats what i've been using the whole time for the sql server.  same test failed error.
maybe it's dns/host name:  should I make a host file for this?
theres no dns server on box.  should i just set a host name on each server?
what you could do then is connect to the IP\Instance_name (i.e. 10.99.200.11\CDI_NETWORK)
I'll try that.  i cannot ping sql  to rds or the other ?
forget the last item.  the boxes can ping each other.
please help!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
resolved!

this was a windows firewall issue.  closing case.
not resolved.  

The issue continues to happen.