Connect To SQL Server Through Socket

Question

Hello everyone, 

I have been trying for days now to connect to my SQL Server through a socket. I can't find what is wrong.

I think I did a correct setup of my sql server, I am able to connect to it from ever the Server manager or Visual Studio with the same connection info that I am using in my Socket.

It works everytime but with the socket. The socket can connect but when it send a string to the server, the SQL Server Logs gives me this:

"length specified in netword packet payload did not match number of bytes read; the connection have been closed

Error: 17836, Severity 20, State 17".

Here is the Socket code I use. It Took it from Microsoft Socket Server/client example:

// ClientMFC.cpp : Defines the entry point for the console application.


#include "stdafx.h"
#include "ClientMFC.h"

#define WIN32_LEAN_AND_MEAN

#include <windows.h>
#include <winsock2.h>
#include <ws2tcpip.h>
#include <stdlib.h>
#include <stdio.h>


// Need to link with Ws2_32.lib, Mswsock.lib, and Advapi32.lib
#pragma comment (lib, "Ws2_32.lib")
#pragma comment (lib, "Mswsock.lib")
#pragma comment (lib, "AdvApi32.lib")



using namespace std;

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
	int nRetCode = 0;

	HMODULE hModule = ::GetModuleHandle(NULL);

	if (hModule != NULL)
	{
		// initialize MFC and print and error on failure
		if (!AfxWinInit(hModule, NULL, ::GetCommandLine(), 0))
		{
			// TODO: change error code to suit your needs
			_tprintf(_T("Fatal Error: MFC initialization failed\n"));
			nRetCode = 1;
		}
		else
		{
			// TODO: code your application's behavior here.
		}
	}
	else
	{
		// TODO: change error code to suit your needs
		_tprintf(_T("Fatal Error: GetModuleHandle failed\n"));
		nRetCode = 1;
	}


    struct addrinfo *result = NULL,
                    *ptr = NULL,
                    hints;

    ZeroMemory( &hints, sizeof(hints) );
    hints.ai_family = AF_INET;//AF_UNSPEC;
    hints.ai_socktype = SOCK_STREAM;
    hints.ai_protocol = IPPROTO_TCP;


    //WSADATA is a struct that is filled up by the call 
    //to WSAStartup
    WSADATA wsaData;


    //WSAStartup initializes the program for calling WinSock.
    //The first parameter specifies the highest version of the 
    //WinSock specification, the program is allowed to use.
    int wsaret=WSAStartup(0x101,&wsaData);

    //WSAStartup returns zero on success.
    //If it fails we exit.
    if(wsaret!=0)
    {
        return 0;
    }

    SOCKET ConnectSocket = INVALID_SOCKET;

    int iResult = getaddrinfo("10.100.17.114", "2433", &hints, &result);
    if (iResult != 0) {
        printf("getaddrinfo failed: %d\n", iResult);
        WSACleanup();
        return 1;
    }

    // Attempt to connect to the first address returned by
    // the call to getaddrinfo
    ptr=result;

    // Create a SOCKET for connecting to server
    ConnectSocket = socket(ptr->ai_family, ptr->ai_socktype, 
        ptr->ai_protocol);

    sockaddr_in serverAddress;
    serverAddress.sin_family=AF_INET;
    serverAddress.sin_port=(USHORT)2433;
    // serverAddress.sin_port=(USHORT)20248;
    serverAddress.sin_addr.s_addr=inet_addr("10.100.17.114");


    if (ConnectSocket == INVALID_SOCKET) {
        printf("Error at socket(): %ld\n", WSAGetLastError());
        freeaddrinfo(result);
        WSACleanup();
        return 1;
    }

   // Connect to server.
     iResult = connect( ConnectSocket, ptr->ai_addr, (int)ptr->ai_addrlen);
    if (iResult == SOCKET_ERROR) {
        closesocket(ConnectSocket);
        ConnectSocket = INVALID_SOCKET;
    }

    // Should really try the next address returned by getaddrinfo
    // if the connect call failed
    // But for this simple example we just free the resources
    // returned by getaddrinfo and print an error message

    freeaddrinfo(result);
    
    const char *   toto   = "<connectionString>Data Source=10.100.17.114,2433;ENCRYPT=false;Initial Catalog=MetricsLocal;Persist Security Info=True;User ID=sa;Password=XXX</connectionString>";

    int BytesSent           = send(ConnectSocket, toto , strlen(toto),0);

    printf("BytesSent = %d, Supposed = %d\n", BytesSent, strlen(toto));

    if (ConnectSocket == INVALID_SOCKET) {
        printf("Unable to connect to server!\n");
        WSACleanup();
        return 1;
    }

	return nRetCode;
}

Note that I have hidden my password.

This code should be really easy for you to try. Just add ws2_32.lib in your linker additional dependancies.

I am really really stuck with this.

I am pretty sure the size is correct. I also coded a Socket server to check what I receive and it seems correct.

Ideas:
- Do I need a header not described anywhere?

-Do I have a special settings to do somewhere ?

My telnet connection tells me my port is open and ready.

Thank you for your help

Answer 1

Hi,

Normally this error occurs if you have some sort of firewall or IDS system detecting port scans. Do you have something that is in place that may be blocking  or throwing this error when you are hitting that port? You can try a packet sniffer like packetizer to see what is occurring but I think you may have something in place you may not be aware of. I will do some more research but I do remember this error had something to do with IDS detecting an intrusion.

Perhaps an MVP or MSFT Eng will reply.

Frank.

---

Frank Garcia

Answer 2

I'm fairly certain that this will never work.  Your app must "communicate" with sql server using a particular protocol - TDS.  Whether that protocol is documented I can't say.  But simply sending the same information over the socket that would normally be specified using a much higher level interface will not work. 

Answer 3

Thanks Frank for the answer. 

I will try packetizer. In the mean time, is there any way to contact a MS Specialist ?

Answer 4

Hello Scott, 

thanks for you answer. I have good reason to believe that this could work. How can I be sure ?

Answer 5

You will have to wait for one to reply to this thread. Sometimes it can take a few days. If this matter is urgent, you can open up a PSS Support call to Microsoft.

http://technet.microsoft.com/en-us/library/dd346877.aspx

Best of luck,

Frank.

---

Frank Garcia

Answer 6

A socket is just a stream (relatively speaking) of bytes.  Most applications are designed to expect a certain pattern of bytes in this stream.  I doubt sql server understands a connection string sent over the socket as a string (ascii or unicode). Perhaps your "good reason" provides a way to verify. 

Answer 7

Thank you Franck !

Answer 8

Maybe you are right on that. 

The fact that I cannot find any type of documentation worries me.

Answer 9

Scott, How could I be sure ?

Answer 10

Without documentation, you cannot.  I seriously doubt you will be able to do this - especially if you are new to socket programming (and it appears that you are based on the code).  You will need to use an inteface supported by sql server. 

Answer 11

Ahahah Ouch !

It is actually some code I found on Microsoft site, I worked it a bit.

I found that: http://msdn.microsoft.com/en-us/library/cc448435.aspx

Looks like it could be my problem

Answer 12

Looks like you are making some progress...

Good Luck!

Frank.

---

Frank Garcia

Answer 13

Hey !

So I found the answer, and yes it is impossible !

I had to write a Server in .Net connected to my code through this socket and then send to the DB with the direct .Net utilities.

So If anyone attempt to do such a thing, do not waste your time.

Thank you all for the help