I have installed ODBC and mssql-tools as per:
But I need to check that I can connect to the MS-SQL Database!
Note that the examples are explicit for the server IP and sometimes for the port as well. The odbc.ini file can be used to specify DSN (the title between []'s). Some applications also need specific driver versions. All of the examples provide an idea of what input and output might be expected, your experience may differ if your login user has limited access.
Once you've installed the required packages, i.e. mssq-tools and Driver Version 17 you can create a /etc/odbc.ini file if one is not prepared for your application:
[mytest]
Driver = ODBC Driver 17 for SQL Server
# Server = [protocol:]server[,port]
# example:
Server = tcp:192.168.100.100,1433
From the terminal, you can make queries using sqlcmd (requires path to be set correctly to sqlcmd/bcp) it can take port with sqlcmd -S ComputerA,1691 :
$ sqlcmd -S 192.168.99.102 -U <sa|username> -P <Password>
1> use master;
2> go
Changed database context to 'master'.
1> select 1;
2> go
-----------
1
(1 rows affected)
or you can use isql:
$ echo "help master" | isql mytest sa <Password> |sed 's/-//g' | sed -e 's/^[[:space:]]*/ /g' -e 's/ / /g'
++
| Connected! |
| |
| sqlstatement |
| help [tablename] |
| quit |
| |
++
SQL> ++++++++++++++++++++++++++++++
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE| TYPE_NAME | COLUMN_SIZE| BUFFER_LENGTH| DECIMAL_DIGITS| NUM_PREC_RADIX| NULLABLE| REMARKS | COLUMN_DEF | SQL_DATA_TYPE| SQL_DATETIME_SUB| CHAR_OCTET_LENGTH| ORDINAL_POSITION| IS_NULLABLE | SS_IS_SPARSE| SS_IS_COLUMN_SET| SS_IS_COMPUTED| SS_IS_IDENTITY| SS_UDT_CATALOG_NAME | SS_UDT_SCHEMA_NAME | SS_UDT_ASSEMBLY_TYPE_NAME | SS_XML_SCHEMACOLLECTION_CATALOG_NAME | SS_XML_SCHEMACOLLECTION_SCHEMA_NAME | SS_XML_SCHEMACOLLECTION_NAME | SS_DATA_TYPE|
++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++
SQLRowCount returns 0
You can also use bcp:
$ bcp master.INFORMATION_SCHEMA.TABLES out OutFile.dat -S <ServerIP> -U sa -P <Password>
Enter the file storage type of field TABLE_CATALOG [nvarchar]:
Enter prefix-length of field TABLE_CATALOG [2]:
Enter field terminator [none]:
Enter the file storage type of field TABLE_SCHEMA [nvarchar]:
Enter prefix-length of field TABLE_SCHEMA [2]:
Enter field terminator [none]:
Enter the file storage type of field TABLE_NAME [nvarchar]:
Enter prefix-length of field TABLE_NAME [2]:
Enter field terminator [none]:
Enter the file storage type of field TABLE_TYPE [char]:
Enter prefix-length of field TABLE_TYPE [2]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]:
Starting copy...
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 8 Average : (750.0 rows per sec.)
# You should not have OutFile.dat:
$ cat OutFile.dat
masterdbospt_fallback_db
BASE TABLE
masterdbo spt_fallback_dev
BASE TABLE
masterdbo spt_fallback_usg
BASE TABLE
masterdbospt_valuesVIEW
masterdbospt_monitor
BASE TABLE
masterdbo*MSreplication_options
And of course, you can use netcat:
apt update && apt install -y netcat
nc -vz <server> <port> # This should return:
mysql [<IP Address>] <Port> (?) open
If you get Connection Refused, you're either pointing at the wrong host, port or there is a firewall blocking access.
Comments
0 comments
Please sign in to leave a comment.