My new job has some sql server 2014. I am an Oracle DBA.
I need to help a client.
They are having issues creating the ODBC connection from their laptop to the database server (or whatever MS calls a database)
I have access to MS SQL Server 2014 Management Studio.
How can I create an ODBC connection to this database on my laptop to test and provide a procedure for the client?
Do I need to create myself a user to test?
(In oracle I would install the client and give them a tnsnames and an alias to use and maybe use tnsping or sqlplus to check the connection)
I use Oracle SQL Develper and it uses jdbc. maybe you can help me setup my first JDBC connection.
Checked if 1433 is enabled and looks like something is connecting to this server;
Proto Local Address Foreign Address State
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
TCP xxx.xx.xx.xxx:3389 xxx.xx.xx.xxx:56010 ESTABLISHED
TCP xxx.xx.xx.xxx:56984 xxx.xx.xx.xxx:2074 ESTABLISHED
If you’re using SQL Server Management Studio (SSMS), you don’t need to “create” a connection via ODBC, you can just connect directly to the server via the SSMS user interface:
In SQL Server, the equivalent of an Oracle “database server” is called a “SQL Server Instance”. It can contain many databases, which are similar to a “schema” in Oracle.
So, when you connect via SSMS, you connect to the instance, which takes the form of:
For a “default, unnamed” instance, or for a “named” instance:
INSTANCENAME gets resolved to a port automatically by the SQL Server Browser Service; if that service is not running, or is blocked by a firewall, you may need to connect using the port number, as in:
0000 is the port number for the instance.
If you don’t have access to the instance, you’ll see an “authentication failed” error message. If SSMS cannot contact the instance because of a firewall, or the server is not listening on the port you specified, you’ll see an error indicating that, similar to:
“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. “
To create a connection via ODBC, you open the ODBC Data Source Administrator from the Windows Control Panel. You can either create a “User DSN” or a “System DSN”, either way choose the
SQL Server Native Client 12.0 driver:
Fill in the details (use the servername as detailed above):
Accept the defaults for the rest of the configuration screens if you plan to use integrated Active Directory domain authentication. If the login will be made using a SQL Server Login, enter those details on this screen:
Thanks to both Max Vernon and Dan Guzman.They both helped me greatly. It was a firewall issue.
- Dan Guzman’s powershell script let me test my changes.
- Max Vernon’s odbc setup instructions let me get connected.
I got admin rights to the server and followed these steps to open an inbound port:
I downloaded and installed the msodbcsql.msi to get the SQL Server odbc client for my laptop.
I created the ODBC datasource.
I downloaded jtds-1.3.1.jar
I followed Oracle’s; “How To Create a Connection With SQL Server In SQL*DEVELOPER” (Doc ID 471882.1)
I was able to get Oracle’s SQL Dev to talk to SQL Server and both the user and I have access to that database.