Sql-server – SQL Server, odbc newbie question

connectivityodbcsql serversql server 2014

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.

Questions:
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)

EDIT1:

I use Oracle SQL Develper and it uses jdbc. maybe you can help me setup my first JDBC connection.

EDIT2:
Tried the ODBC data source and it failed
enter image description here

EDIT3:
Checked if 1433 is enabled and looks like something is connecting to this server;

Active Connections

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

Best Answer

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:

enter image description here

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:

COMPUTERNAME

For a "default, unnamed" instance, or for a "named" instance:

COMPUTERNAME\INSTANCENAME

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:

tcp://COMPUTERNAME,0000

where 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:

enter image description here

Fill in the details (use the servername as detailed above):

enter image description here

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:

enter image description here