none
Bulk copy functions support on SQL server with force encryption enabled RRS feed

  • Question

  • Hi,

    My application works fine before my SQL server enabled the "force encryption" in SQL configuration manager. For security reason, I enabled "Force encryption" for my SQL server to make the connection go with TLS 1.2. 

    In client side, I updated my ODBC driver to 11 and enabled encryption as well. The test connection from the DSN administrator odbcad32.exe is successfully.

    My application can connect to SQL server using TLS 1.2 successfully, and execute some queries. However, the bulk copy functions do not work after the encryption is enabled. There is no error returned from bcp_init(), but the bcp operations after bcp_init() get the Function Sequence Error.

    However, the bulk copy functions do not work after the encryption is enabled. There is no error returned from bcp_init(), but the bcp operations after bcp_init() get the Function Sequence Error.

    The error message from ODBC driver is: retcode:0  state:HY010  MessageText:[Microsoft][ODBC Driver 11 for SQL Server]Function sequence error.

    Are bulk copy functions supported when force encryption is enabled? Thanks.



    • Edited by Jill Chen Thursday, September 14, 2017 7:58 AM
    Thursday, September 14, 2017 7:54 AM

All replies

  • Hi,

    My application works fine before my SQL server enabled the "force encryption" in SQL configuration manager. For security reason, I enabled "Force encryption" for my SQL server to make the connection goes with TLS 1.2. 

    In client side, I updated my ODBC driver to 11 and enabled encryption as well. The test connection from the DSN administrtor odbcad32.exe is successfully.

    My application can connect to SQL server with TLS 1.2 successfully and execute some queries. However, the bulk copy functions do not work after the encryption is enabled. There is no error returned from bcp_init(), but the bcp operations after bcp_init() get the Function Sequence Error.

    <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px 'Lucida Grande'; color: #ff2600} span.s1 {color: #000000} </style> The error message from ODBC driver is: retcode:0  state:HY010  MessageText:[Microsoft][ODBC Driver 11 for SQL Server]Function sequence error.

    Are bulk copy functions supported when force encryption is enabled? Thanks.

    Thursday, September 14, 2017 8:01 AM
  • I would suggest that you download and install the ODBC 13.1 SQL Server driver, that is, the latest version, to see if this resolves the issue.

    Thursday, September 14, 2017 9:33 PM
  • Thanks for the reply. I installed ODBC 13.1 SQL Server driver, but the result is the same. Any other thoughts?
    Friday, September 15, 2017 2:21 AM
  • Hi Jill Chen,

     

    There does not exist a limitation for bcp_init() using encrypted connection.

     

    Based on my searching, this is a problem which related to the ODBC driver, did you use the latest version of ODBC?

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, September 15, 2017 6:26 AM
    Moderator
  • Looks like a roadblock then.

    If this is a blocking issue for you, you could open a case with Microsoft, and if your business case (and support contract!) is good enough, you may get a fix. But they may also say this is a known limitation.

    I'm looking at the output from BCP -?, and there is no encryption option listed, whereas SQLCMD has one.

    That is, unforunately, a token of that this is an intended limitation.

    Friday, September 15, 2017 9:57 PM
  • Hi Teige,

    I tried the latest version 13.1 ODBC driver, and I got this error "Connection is not enabled for BCP (0) " in the trace log.

    " EventCollecto 1194-1b5c ENTER SQLGetInfoW 
    HDBC                0x0093A688
    UWORD                       76 <SQL_DRIVER_HLIB>
    PTR                 0x0338F45C
    SWORD                        0 
    SWORD *             0x0338F424

    " EventCollecto 1194-1b5c EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
    HDBC                0x0093A688
    UWORD                       76 <SQL_DRIVER_HLIB>
    PTR                 0x0338F45C ( 0x72480000)
    SWORD                        0 
    SWORD *             0x0338F424 (4)

    " EventCollecto 1194-1b5c ENTER SQLSetConnectAttr 
    SQLHDBC             0x0093A688
    SQLINTEGER              -28236 <unknown>
    SQLPOINTER          [Unknown attribute -28236]
    SQLINTEGER                  -5 

    " EventCollecto 1194-1b5c EXIT  SQLSetConnectAttr  with return code -1 (SQL_ERROR)
    SQLHDBC             0x0093A688
    SQLINTEGER              -28236 <unknown>
    SQLPOINTER          [Unknown attribute -28236]
    SQLINTEGER                  -5 

    DIAG [HY000] [Microsoft][ODBC Driver 13 for SQL Server]Connection is not enabled for BCP (0) 

    It looks like while bcp_init(), it sends SQLSetConnectAttr again and got the error. But the handle was allocated and set the connection attribute BCP_COPT_SS_BCP and SQL_BCP_ON before connecting to the SQL server like below.

    " EventCollecto 1194-1b5c EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
    SQLSMALLINT                  2 <SQL_HANDLE_DBC>
    SQLHANDLE           0x0093A608
    SQLHANDLE *         0x01CD8FDC ( 0x0093A688)

    " EventCollecto 1194-1b5c ENTER SQLSetConnectAttr 
    SQLHDBC             0x0093A688
    SQLINTEGER                1219 <unknown>
    SQLPOINTER          [Unknown attribute 1219]
    SQLINTEGER                  -6 

    " EventCollecto 1194-1b5c EXIT  SQLSetConnectAttr  with return code 0 (SQL_SUCCESS)
    SQLHDBC             0x0093A688
    SQLINTEGER                1219 <unknown>
    SQLPOINTER          [Unknown attribute 1219]
    SQLINTEGER                  -6 

    " EventCollecto 1194-1b5c ENTER SQLDriverConnectW 
    HDBC                0x0093A688
    HWND                0x00000000
    WCHAR *             0x73761F7C [      -3] "******\ 0"
    SWORD                       -3 
    WCHAR *             0x73761F7C 
    SWORD                       -3 
    SWORD *             0x00000000
    UWORD                        0 <SQL_DRIVER_NOPROMPT>

    " EventCollecto 1194-1b5c EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
    HDBC                0x0093A688
    HWND                0x00000000
    WCHAR *             0x73761F7C [      -3] "******\ 0"
    SWORD                       -3 
    WCHAR *             0x73761F7C <Invalid buffer length!> [-3]
    SWORD                       -3 
    SWORD *             0x00000000
    UWORD                        0 <SQL_DRIVER_NOPROMPT>

    In summary, the oder of the calls is:

    1. Allocate handle

    2. Set connection attribute (1219) ->  BCP_COPT_SS_BCP

    3. Connect to server (successfully)

    4. bcp_init (failed) -> it is going to set connection attribute (-28236).

    I'm not sure what the attribute -28236 means? Do you have any idea?

    Thanks.



    • Edited by Jill Chen Monday, September 25, 2017 6:20 AM
    Monday, September 25, 2017 6:05 AM
  • Hi Jill,

    As mentioned by Erland, I suggest you opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com).

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, October 10, 2017 8:18 AM
    Moderator
  • Hello, i`ve the same problem, can you tell me how you solved that? please send information to e-mail pusherik1337@gmail.com or reply this.
    Thanks, regards, Dimitry.
    Thursday, June 6, 2019 8:37 AM
  • Hello, i`ve the same problem, can you tell me how you solved that? please send information to e-mail pusherik1337@gmail.com or reply this.

    If you have a problem, you should start a new thread and describe your problem from start to end and not piggyback on an old thread. It may seem that you have "the same problem", but the underlying reason may be different. Or the solution may have changed over the years, if you are on a newer version.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 10, 2019 9:21 PM