Cannot connect to SQL Azure database

[mcartoixa]

I have used tedious succesfully in the past to connect to various stanadrd SQL Server instances. I have spent the whole day to try and connect to an database hosted on Azure, to no avail.

At this point I have activated the debug options (available here) and it seems that it fact a connection is established, but is closed right after the login has been sent. The encrypt option is set to true, and I can connect to the database with the same credentials by other means (SSMS).

I am using the latest 1.5.1 version.

Any idea?

[rossipedia]

This might have something to do with the recent Availability Group stuff I put in place. Could you possibly post the debug output so I can take a look?

[mcartoixa]

Sorry, my previous post did not make it clear: the output is available at https://gist.github.com/mcartoixa/c32db3279510775c8ce4

[rossipedia]

Hmmm.... that's odd. can you set the following debug options and show me the output?

debug: {
  packet: true,
  data: true,
  payload: true,
  token: true
}

This'll turn on all available debugging output.

[mcartoixa]

I've set up a (temporary) test case so that no information contained in the output should be sensitive. This way you could also reproduce the behaviour.

The connection options:

{ options: { debug: [Object], database: 'Tedious.Test' },
 server: 'd31kysgmf5.database.windows.net',
 userName: 'tedious.user@d31kysgmf5',
 password: 'p@ssw0rd',
 encrypt: true }

The debug output:

connected to d31kysgmf5.database.windows.net:1433

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
  0000  00001A00 06010020 00010200 21000103 00220004 04002600 01FF0000 00010001  ....... ....!... ."....&. ........
  0020  02000000 000000  .......
  PreLogin - version:0.0.0.1 1, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin

Received
  type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x002B, spid:0x0000, packetId:0x01, window:0x00
  0000  00001A00 06010020 00010200 21000103 00220000 04002200 01FF0B00 04DF0002  ....... ....!... ."....". ........
  0020  030000  ...
  PreLogin - version:11.0.4.223 2, encryption:0x03(REQ), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)

Sent
  type:0x10(LOGIN7), status:0x01(EOM), length:0x00E4, spid:0x0000, packetId:0x01, window:0x00
  0000  DC000000 04000074 00100000 00000000 20180000 00000000 E0000008 88FFFFFF  .......t........  ....... ........
  0020  09040000 5E000600 6A001700 98000800 A8000700 B6000000 B6000000 B6000700  ....^... j....... ........ ........
  0040  C4000000 C4000C00 01020304 0506DC00 0000DC00 0000DC00 00000000 00005700  ........ ........ ........ ......W.
  0060  53002D00 4D004100 43007400 65006400 69006F00 75007300 2E007500 73006500  S.-.M.A. C.t.e.d. i.o.u.s. ..u.s.e.
  0080  72004000 64003300 31006B00 79007300 67006D00 66003500 A2A5A1A5 92A592A5  r.@.d.3. 1.k.y.s. g.m.f.5. ........
  00A0  D2A5A6A5 82A5E3A5 54006500 64006900 6F007500 73005400 65006400 69006F00  ........ T.e.d.i. o.u.s.T. e.d.i.o.
  00C0  75007300 54006500 64006900 6F007500 73002E00 54006500 73007400   u.s.T.e. d.i.o.u. s...T.e. s.t.
  Login7 - TDS:0x74000004, PacketSize:0x00001000, ClientProgVer:0x00000000, ClientPID:0x00001820, ConnectionID:0x00000000
           Flags1:0xE0, Flags2:0x00, TypeFlags:0x00, Flags3:0x08, ClientTimezone:-120, ClientLCID:0x00000409
           Hostname:'WS-MAC', Username:'tedious.user@d31kysgmf5', Password:'p@ssw0rd', AppName:'Tedious', ServerName:'undefined', LibraryName:'Tedious'
           Language:'undefined', Database:'Tedious.Test', SSPI:'', AttachDbFile:'', ChangePassword:''
State change: SentPrelogin -> SentLogin7WithStandardLogin
socket ended
State change: SentLogin7WithStandardLogin -> Final
connection to d31kysgmf5.database.windows.net:1433 closed
State is already Final

[rossipedia]

I was unable to connect to that particular server, but I did notice that you had encrypt: true directly on the config object. That flag needs to be on the options object (per the docs). Can you move that over and see if that clears things up?

{
    options: {
        debug: { ... },
        database: 'Tedious.Test',
        encrypt: true
    },
    server: 'd31kysgmf5.database.windows.net',
    userName: 'tedious.user@d31kysgmf5',
    password: 'p@ssw0rd'
}

[mcartoixa]

Indeed, the correct answer was RTFM. Sorry for wasting your time.

Keep up the good work 👍

[rossipedia]

No worries! That's what we're here for :)

[vvo]

@rossipedia Should we automatically put encrypt:true when windows.net in the hostname?

[bretcope]

I don't think inferring configuration is the road we want to go down. What might be a good idea is to include a section in the documentation about connecting to Azure.

[arthurschreiber]

Like this? ccf8573

[bretcope]

yes... like that.

[TyrionLanister]

Hej @rossipedia @arthurschreiber @vvo
I am connecting to an Azure database. But I am receiving connnection error with code: Elogin

name: 'ConnectionError',
message: 'Login failed for user 'd*******'.',
code: 'ELOGIN'

var config = {
    userName: '****@*****.com',
    password: '****',
    server: '******.database.windows.net',
    options: { encrypt: 'true', database: 'sensorData'}};

Detailed log is here=>

connected to abcd.database.windows.net:1433
Connected to Database sensorData
Sent
type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
0000 00001A00 06010020 00010200 21000103 00220004 04002600 01FF0000 00010001 ....... ....!... ."....&. ........
0020 01000000 000000 .......
PreLogin - version:0.0.0.1 1, encryption:0x01(ON), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin
Connected to Database sensorData
Received
type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x002B, spid:0x0000, packetId:0x01, window:0x00
0000 00001A00 06010020 00010200 21000103 00220000 04002200 01FF0B00 04DF0002 ....... ....!... ."....". ........
0020 010000 ...
PreLogin - version:11.0.4.223 2, encryption:0x01(ON), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
Connected to Database sensorData
Login7 - TDS:0x74000004, PacketSize:0x00001000, ClientProgVer:0x00000000, ClientPID:0x0000340C, ConnectionID:0x00000000
Flags1:0xE0, Flags2:0x00, TypeFlags:0x00, Flags3:0x08, ClientTimezone:-120, ClientLCID:0x00000409
Hostname:' ', Username:' ', Password:' ', AppName:'Tedious', ServerName:'abc.database.windows.net', LibraryName:'Tedious'
Language:'undefined', Database:'sensorData', SSPI:'', AttachDbFile:'', ChangePassword:''
State change: SentPrelogin -> SentTLSSSLNegotiation
Connected to Database sensorData
Sent
type:0x12(PRELOGIN), status:0x01(EOM), length:0x0145, spid:0x0000, packetId:0x01, window:0x00
Connected to Database sensorData
Received
type:0x12(PRELOGIN), status:0x01(EOM), length:0x0DED, spid:0x0000, packetId:0x01, window:0x00
Connected to Database sensorData
Sent
type:0x12(PRELOGIN), status:0x01(EOM), length:0x00AE, spid:0x0000, packetId:0x01, window:0x00
0000 16030100 66100000 62610404 89529B89 5A209E37 B1443BD0 BF9FDA0B 5B6BED84 ....f... ba...R.. Z .7.D;. ....[k..
0020 85D42CD3 05686E90 8FC18968 C3DCB1E4 39ECB53F 118F49CE C652876B 2B99DC35 ..,..hn. ...h.... 9..?..I. .R.k+..5
0040 A547C90F 9201D64C 4EADD5A8 3ABB1377 FC87F4CE 062DA2B9 DC9BA02D C926B803 .G.....LN...:..w.....-.. ...-.&..
0060 27CBC849 82831957 5CAACF14 03010001 01160301 00306417 065D1796 B0DDDF77 '..I...W....... .....0d. .].....w
0080 CA62DEDF AF8AFE52 547172C0 51BB58E9 BD252B64 753D415D FDEB513D DA848499 .b.....RTqr.Q.X. .%+du=A]..Q=....
00A0 29925000 6787 ).P.g.
Connected to Database sensorData
Received
type:0x12(PRELOGIN), status:0x01(EOM), length:0x0043, spid:0x0000, packetId:0x01, window:0x00
0000 14030100 01011603 010030F9 2601E838 5261DD93 4A950896 B55E959F BA78B84F ........ ..0.&..8Ra..J... .^...x.O
0020 4FEA6B22 92265B0C A01687B1 F6154ECB 42438F60 C01D0022 B33334 O.k".&[. ......N. BC.`...".34
TLS negotiated (ECDHE-RSA-AES256-SHA, TLSv1/SSLv3)
State change: SentTLSSSLNegotiation -> SentLogin7WithStandardLogin
Connected to Database sensorData
Received
{ number: 18456,
state: 1,
class: 14,
message: 'Login failed for user '*****'.',
serverName: ' ',
procName: '',
lineNumber: 1,
name: 'ERROR',
event: 'errorMessage' }
{ number: 40608,
state: 1,
class: 10,
message: 'This session has been assigned a tracing ID of '89796195-3696-42c1-a1ae-2bec2f258328'. Provide this tracing ID to customer support when you need assistance.',
serverName: ' ',
procName: '',
lineNumber: 1,
name: 'INFO',
event: 'infoMessage' }
{ name: 'DONE',
event: 'done',
more: false,
sqlError: true,
attention: false,
serverError: false,
rowCount: undefined,
curCmd: 0 }
State change: SentLogin7WithStandardLogin -> Final
connection to abc.database.windows.net:1433 closed
State is already Final "

I am new to tedious. Any help will be greatly appreciated.
@bretcope Is there any updates on documentation for connecting to azure

[ReadySoftTeam]

A mi me funciono de esta forma:

const sequelize = new Sequelize('DB Name', 'Username', 'Password', {
host: 'Host',
dialect: 'mssql',
dialectOptions: {
options: {
encrypt: true,
}
}
});