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
metalfubarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
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.
metalfubarAuthor Commented:
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?
metalfubarAuthor Commented:
do i need sql server installed?  Please help!
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Is the firewall on and if so, is it configured correctly?  You should not need SQL Manager for your app to work.
metalfubarAuthor Commented:
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?
ZberteocCommented:
You need the ports enabled on both inbound and outbond. The data is changed both ways between the 2 boxes.
metalfubarAuthor Commented:
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!
ZberteocCommented:
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.
metalfubarAuthor Commented:
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.
metalfubarAuthor Commented:
can anyone help me out there?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
metalfubarAuthor Commented:
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?
metalfubarAuthor Commented:
I'm turning firewall off for testing.
metalfubarAuthor Commented:
the same issue occurs.
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Could you provide the output for the following commands:

net start
netstat -b
metalfubarAuthor Commented:
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
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
OK...to connect to the SQL, using CDI-SQL-2K12\CDI_NETWORK for the SQL server name and try again.
metalfubarAuthor Commented:
thats what i've been using the whole time for the sql server.  same test failed error.
metalfubarAuthor Commented:
maybe it's dns/host name:  should I make a host file for this?
metalfubarAuthor Commented:
theres no dns server on box.  should i just set a host name on each server?
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
what you could do then is connect to the IP\Instance_name (i.e. 10.99.200.11\CDI_NETWORK)
metalfubarAuthor Commented:
I'll try that.  i cannot ping sql  to rds or the other ?
metalfubarAuthor Commented:
forget the last item.  the boxes can ping each other.
metalfubarAuthor Commented:
please help!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
What about http:#a41264602 ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
metalfubarAuthor Commented:
resolved!

this was a windows firewall issue.  closing case.
metalfubarAuthor Commented:
not resolved.  

The issue continues to happen.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.