Asset import tool - Windows Authentication not working

Lyonel · August 8, 2018

Hi,

Just picking up an old topic (as its the summer holidays, we have a bit more time). Having upgraded to the latest version on github, we ran an Asset import with minimal changes to the configuration. It crashed...

The same configuration used to work before, but it no longer works now.

"SQLConf": {
      "Driver": "mssql",
      "Server": "MyServer\\Test",
      "Database": "Audit",
      "Authentication": "Windows",
      "UserName": "DOMAIN\\HornbillUser",
      "Password": "TestHornbill",
      "Port": 1433,
     "AssetID": "MachineName",
     "Encrypt": false,
     "Query": "SELECT * FROM ... "
 }

Turned off the windows authentication and used a SQL authentication instead => worked.

"SQLConf": {
      "Driver": "mssql",
      "Server": "MyServer\\Test",
      "Database": "Audit",
      "Authentication": "SQL",
      "UserName": "TestHornbill",
      "Password": "TestHornbill",
      "Port": 1433,
     "AssetID": "MachineName",
     "Encrypt": false,
     "Query": "SELECT * FROM ... "
 }

Question: is there a known issue around MSSQL and Windows authentication via the tool? If not, could you look into it?

Either we have windows authentication possible and it works, or you should remove it, to avoid confusion.

Thanks,

Lyonel

Steve G · August 8, 2018

Hi @Lyonel,

I've just tested the latest version of the tool with Windows authentication to an MS SQL Server containing an asset database, and it works fine.

What was the actual crash error message?

One thing to note: the content of the username and password fields in the SQLConf section of the config are ignored if you choose Windows as your authentication method, as it actually authenticates using the Windows session account details that the tool is run within. So the account that actually runs the tool (either your account if you're running it locally, or the system, account used when scheduling the tool to run outside of an interactive session) needs access to your database. Just use empty strings for your values of Username and Password.

Thanks,

Steve

Lyonel · August 8, 2018

6 minutes ago, Steve G said:

One thing to note: the content of the username and password fields in the SQLConf section of the config are ignored if you choose Windows as your authentication method, as it actually authenticates using the Windows session account details that the tool is run within. So the account that actually runs the tool (either your account if you're running it locally, or the system, account used when scheduling the tool to run outside of an interactive session) needs access to your database. Just use empty strings for your values of Username and Password.

That would be worth mentioning in the wiki!

I already considered that was the case (as I could not see any other possible technical implementation without greating a security nightmare) so I already tried running the tool under my session (DBA => Full access to everything on the database server) and it does not work. Emptying the login and password field says that the configuration is invalid

Error message:

[DEBUG] Connecting to Database Server: MyServer\Production
[DATABASE] Connection Successful
[DATABASE] Running database query for Virtual Machine assets. Please wait...
[ERROR]  [DATABASE] Database Query Error: driver: bad connection

Steve G · August 8, 2018

@Lyonel

I'll update the Wiki to mention that, thanks.

Looking at the code (as it was a LONG time since I wrote that ), those fields are mandatory, so I'll remove that in the next release when Windows is the authentication method. I've just stuck a letter "a" in my config for testing, and it does work ok.

The connection issue is an odd one. What version of SQL Server are your asset records held on, and is it set to enforce encryption? If it's SQL Server 2008 R2 Service Pack 2 or above, can you try enabling encryption in the import tool SQLConf, and see if that helps? Encryption should only be set to false on SQL Server 2008 R2 or below, as they contained a defect with the handling of encrypted login records.

Thanks,

Steve

Steve G · August 15, 2018

@Lyonel,

Did you manage to get this working?

Also, I've just released v1.5.0 of the database asset import tool which makes the UserName and Password fields non-mandatory when using Windows authentication (plus some other enhancements). Details on the wiki and the Github repo.

Steve

Lyonel · August 28, 2018

@Steve G sorry for the delayed answer, I was on annual leave until today.

We are trying to connect to SQL Server 2016 with encryption set to false.

In v1.5.0, I can get the following error message when leaving empty string for UserName and Password:

compared to 1.4.2:

Still no luck getting it to work using Windows Authentication I am afraid.

Lyonel · August 28, 2018

Just to be clear, here my setup:

"SQLConf": {
      "Driver": "mssql",
      "Server": "MyServer\\Test",
      "Database": "Audit",
      "Authentication": "Windows",
      "UserName": "",
      "Password": "",
      "Port": 1433,
     "AssetID": "MachineName",
     "Encrypt": false,
     "Query": "SELECT * FROM ... "
 }

Victor · August 29, 2018

@Lyonel so you said:

23 hours ago, Lyonel said:

trying to connect to SQL Server 2016 with encryption set to false.

and @Steve G said:

On 8/8/2018 at 4:02 PM, Steve G said:

Encryption should only be set to false on SQL Server 2008 R2 or below

2016 > 2008... so...

Lyonel · August 29, 2018

Ok ok @Victor...

But even with Encrypt: true I get this:

2018/08/29 14:09:16 [DEBUG] ---- XMLMC Database Asset Import Utility V1.5.0 ----
2018/08/29 14:09:16 [DEBUG] Flag - Config File conf_sccm_assetscomputer.json
2018/08/29 14:09:16 [DEBUG] Flag - Zone eur
2018/08/29 14:09:16 [DEBUG] Flag - Dry Run false
2018/08/29 14:09:16 [DEBUG] Loading Config File: E:\Hornbill Connectivity\SCCM connectivityNew/conf_sccm_assetscomputer.json
2018/08/29 14:09:16 [ERROR] Error Decoding Configuration File: json: cannot unmarshal object into Go struct field sqlImportConfStruct.AssetTypes of type []main.assetTypesStruct
2018/08/29 14:09:16 [DEBUG] Updated: 0
2018/08/29 14:09:16 [DEBUG] Updated Skipped: 0
2018/08/29 14:09:16 [DEBUG] Created: 0
2018/08/29 14:09:16 [DEBUG] Created Skipped: 0
2018/08/29 14:09:16 [DEBUG] Time Taken: 8.0008ms
2018/08/29 14:09:16 [DEBUG] ---- XMLMC Database Asset Import Complete ----

TrevorKillick · August 29, 2018

@Lyonel

This error is now suggesting a problem with the configuration file, can you post up a copy of it minus and credentials of course.

Kind Regards

Trevor Killick

Lyonel · August 29, 2018

{
  "APIKey": "xxxxxxx",
  "InstanceID": "xyz",
  "AssetIdentifier":"h_name",
  "LogSizeBytes":1000000,
  "SQLConf": {
     "Driver": "mssql",
     "Server": "myserver\\myinstance",
     "Database": "Audit",
     "Authentication": "SQL",
     "UserName": "",
     "Password": "",
     "Port": 1433,
     "AssetID": "MachineName",
     "Encrypt": true,
     "Query": "SELECT MachineName ,SCCMMachineName ,ChassisTypes0 ,SiteName ,ActualRetiredDate ,Country ,CreatedDate ,SystemModel ,Location ,Notes ,OwnedByName ,ScheduledRetireDate ,UsedByName ,MacAddress ,IPAddress ,SystemManufacturer ,ProcessorName ,LastLogon ,LastLogonUser ,MemoryKB ,OperatingSystem ,SerialNumber ,ServicePackVersion ,OperatingSystemVersion ,ProcessorSpeedGHz , NumberofProcessors ,BIOSDescription ,BIOSReleaseDate ,SMBIOSVersion FROM uv_Hornbill_Assets "
 },
 "AssetTypes": {
     "Server": "WHERE ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23)",
     "Laptop": "WHERE ChassisTypes0 IN (8, 9, 10, 14)",
     "Desktop": "WHERE ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16, 17)",
     "Virtual Machine":"WHERE ChassisTypes0 = 1"
 },
 "AssetGenericFieldMapping":{
     "h_name":"[MachineName]",
     "h_site":"[SiteName]",
     "h_asset_tag":"[MachineName]",
     "h_acq_method":"",
     "h_actual_retired_date":"[ActualRetiredDate]",
     "h_beneficiary":"",
     "h_building":"",
     "h_cost":"",
     "h_cost_center":"",
     "h_country":"",
     "h_created_date":"",
     "h_deprec_method":"",
     "h_deprec_start":"",
     "h_description":"[MachineName] ([SystemModel])",
     "h_disposal_price":"",
     "h_disposal_reason":"",
     "h_floor":"",
     "h_geo_location":"",
     "h_invoice_number":"",
     "h_location":"",
     "h_location_type":"",
     "h_maintenance_cost":"",
     "h_maintenance_ref":"",
     "h_notes":"[Notes]",
     "h_operational_state":"",
     "h_order_date":"",
     "h_order_number":"",
     "h_owned_by":"[OwnedByName]",
     "h_owned_by_name":"[OwnedByName]",
     "h_product_id":"",
     "h_received_date":"[CreatedDate]",
     "h_residual_value":"",
     "h_room":"",
     "h_scheduled_retire_date":"[ScheduledRetireDate]",
     "h_supplier_id":"",
     "h_supported_by":"",
     "h_used_by":"[OwnedByName]",
     "h_used_by_name":"[OwnedByName]",
     "h_version":"",
     "h_warranty_expires":"",
     "h_warranty_start":""
 },
 "AssetTypeFieldMapping":{
     "h_name":"[MachineName]",
     "h_mac_address":"[MACAddress]",
     "h_net_ip_address":"[IPAddress]",
     "h_net_computer_name":"[MachineName]",
     "h_net_win_domain":"[NETDomain]",
     "h_model":"[SystemModel]",
     "h_manufacturer":"[SystemManufacturer]",
     "h_cpu_info":"[ProcessorName]",
     "h_description":"[SystemModel]",
     "h_last_logged_on":"[LastLogon]",
     "h_last_logged_on_user":"[OwnedByName]",
     "h_memory_info":"[MemoryKB]",
     "h_net_win_dom_role":"",
     "h_optical_drive":"",
     "h_os_description":"[OperatingSystem]",
     "h_os_registered_to":"",
     "h_os_serial_number":"",
     "h_os_service_pack":"[ServicePackVersion]",
     "h_os_type":"",
     "h_os_version":"[OperatingSystemVersion]",
     "h_physical_disk_size":"[DiskSpaceMB]",
     "h_serial_number":"[SystemSerialNumber]",
     "h_cpu_clock_speed":"[ProcessorSpeedGHz]",
     "h_physical_cpus":"[NumberofProcessors]",
     "h_logical_cpus":"",
     "h_bios_name":"[BIOSDescription]",
     "h_bios_manufacturer":"",
     "h_bios_serial_number":"",
     "h_bios_release_date":"[BIOSReleaseDate]",
     "h_bios_version":"[SMBIOSVersion]",
     "h_max_memory_capacity":"",
     "h_number_memory_slots":"",
     "h_net_name":"",
     "h_subnet_mask":""
 }
}

TrevorKillick · August 29, 2018

@Lyonel

Perfect thank you, looking at the code and the Github docs the AssetTypes section has changed in recent versions of the tool to the following format:

"AssetTypes": [
    {
        "AssetType": "Server",
        "Query": "AND OASysEncl.ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Laptop",
        "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Desktop",
        "Query": "AND OASysEncl.ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16, 17)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Virtual Machine",
        "Query": "AND OASysEncl.ChassisTypes0 = 1",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    }
  ],

If you update this part of the configuration file it should resolve this new error you are seeing.

Kind Regards

Trevor Killick

Lyonel · August 29, 2018

@TrevorKillick thanks for the update.

I replaced the configuration file as per your comment, but I am still getting an error:

2018/08/29 15:59:42 [DEBUG] ---- XMLMC Database Asset Import Utility V1.5.0 ----
2018/08/29 15:59:42 [DEBUG] Flag - Config File conf_sccm_assetscomputer.json
2018/08/29 15:59:42 [DEBUG] Flag - Zone eur
2018/08/29 15:59:42 [DEBUG] Flag - Dry Run false
2018/08/29 15:59:42 [DEBUG] Loading Config File: E:\Hornbill Connectivity\SCCM connectivityNew/conf_sccm_assetscomputer.json
2018/08/29 15:59:42 [ERROR] Error Decoding Configuration File: invalid character 'ï' looking for beginning of value
2018/08/29 15:59:42 [DEBUG] Updated: 0
2018/08/29 15:59:42 [DEBUG] Updated Skipped: 0
2018/08/29 15:59:42 [DEBUG] Created: 0
2018/08/29 15:59:42 [DEBUG] Created Skipped: 0
2018/08/29 15:59:42 [DEBUG] Time Taken: 17.0017ms
2018/08/29 15:59:42 [DEBUG] ---- XMLMC Database Asset Import Complete ----

Config:

{
  "APIKey": "xxxxxx",
  "InstanceID": "xyz",
  "AssetIdentifier":"h_name",
  "LogSizeBytes":1000000,
  "SQLConf": {
     "Driver": "mssql",
     "Server": "myserver\\myinstance",
     "Database": "Audit",
	 "Authentication": "SQL",
     "UserName": "",
     "Password": "",
     "Port": 1433,
     "AssetID": "MachineName",
     "Encrypt": true,
     "Query": "SELECT MachineName ,SCCMMachineName ,ChassisTypes0 ,SiteName ,ActualRetiredDate ,Country ,CreatedDate ,SystemModel ,Location ,Notes ,OwnedByName ,ScheduledRetireDate ,UsedByName ,MacAddress ,IPAddress ,SystemManufacturer ,ProcessorName ,LastLogon ,LastLogonUser ,MemoryKB ,OperatingSystem ,SerialNumber ,ServicePackVersion ,OperatingSystemVersion ,ProcessorSpeedGHz , NumberofProcessors ,BIOSDescription ,BIOSReleaseDate ,SMBIOSVersion FROM uv_Hornbill_Assets "
 },
 "AssetTypes": [
    {
        "AssetType": "Server",
        "Query": "WHERE ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Laptop",
        "Query": "WHERE ChassisTypes0 IN (8, 9, 10, 14)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Desktop",
        "Query": "WHERE ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16, 17)",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    },
    {
        "AssetType": "Virtual Machine",
        "Query": "WHERE ChassisTypes0 = 1",
        "AssetIdentifier": {
            "DBColumn": "MachineName",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        }
    }
  ],
 "AssetGenericFieldMapping":{
     "h_name":"[MachineName]",
     "h_site":"[SiteName]",
     "h_asset_tag":"[MachineName]",
     "h_acq_method":"",
     "h_actual_retired_date":"[ActualRetiredDate]",
     "h_beneficiary":"",
     "h_building":"",
     "h_cost":"",
     "h_cost_center":"",
     "h_country":"",
     "h_created_date":"",
     "h_deprec_method":"",
     "h_deprec_start":"",
     "h_description":"[MachineName] ([SystemModel])",
     "h_disposal_price":"",
     "h_disposal_reason":"",
     "h_floor":"",
     "h_geo_location":"",
     "h_invoice_number":"",
     "h_location":"",
     "h_location_type":"",
     "h_maintenance_cost":"",
     "h_maintenance_ref":"",
     "h_notes":"[Notes]",
     "h_operational_state":"",
     "h_order_date":"",
     "h_order_number":"",
     "h_owned_by":"[OwnedByName]",
     "h_owned_by_name":"[OwnedByName]",
     "h_product_id":"",
     "h_received_date":"[CreatedDate]",
     "h_residual_value":"",
     "h_room":"",
     "h_scheduled_retire_date":"[ScheduledRetireDate]",
     "h_supplier_id":"",
     "h_supported_by":"",
     "h_used_by":"[OwnedByName]",
     "h_used_by_name":"[OwnedByName]",
     "h_version":"",
     "h_warranty_expires":"",
     "h_warranty_start":""
 },
 "AssetTypeFieldMapping":{
     "h_name":"[MachineName]",
     "h_mac_address":"[MACAddress]",
     "h_net_ip_address":"[IPAddress]",
     "h_net_computer_name":"[MachineName]",
     "h_net_win_domain":"[NETDomain]",
     "h_model":"[SystemModel]",
     "h_manufacturer":"[SystemManufacturer]",
     "h_cpu_info":"[ProcessorName]",
     "h_description":"[SystemModel]",
     "h_last_logged_on":"[LastLogon]",
     "h_last_logged_on_user":"[OwnedByName]",
     "h_memory_info":"[MemoryKB]",
     "h_net_win_dom_role":"",
     "h_optical_drive":"",
     "h_os_description":"[OperatingSystem]",
     "h_os_registered_to":"",
     "h_os_serial_number":"",
     "h_os_service_pack":"[ServicePackVersion]",
     "h_os_type":"",
     "h_os_version":"[OperatingSystemVersion]",
     "h_physical_disk_size":"[DiskSpaceMB]",
     "h_serial_number":"[SystemSerialNumber]",
     "h_cpu_clock_speed":"[ProcessorSpeedGHz]",
     "h_physical_cpus":"[NumberofProcessors]",
     "h_logical_cpus":"",
     "h_bios_name":"[BIOSDescription]",
     "h_bios_manufacturer":"",
     "h_bios_serial_number":"",
     "h_bios_release_date":"[BIOSReleaseDate]",
     "h_bios_version":"[SMBIOSVersion]",
     "h_max_memory_capacity":"",
     "h_number_memory_slots":"",
     "h_net_name":"",
     "h_subnet_mask":""
 }
}

Victor · August 29, 2018

You going to throw something at me now but I'll say it none the less... what about turning encryption OFF?

Lyonel · August 29, 2018

@Victor does not make a difference. Same error message

TrevorKillick · August 30, 2018

@Lyonel

Just as an explanation, whenever you see an error like that its due to the JSON configuration file being invalid throwing it into JSON Lint returns this:

https://jsonlint.com/

Kind Regards

Trevor Killick

Lyonel · August 30, 2018

Thanks @TrevorKillick! That is one tool I did not know about and would have great use of!

So I removed the invalid character, and back to "database configuration not set" message:

2018/08/30 10:07:14 [DEBUG] ---- XMLMC Database Asset Import Utility V1.5.0 ----
2018/08/30 10:07:14 [DEBUG] Flag - Config File conf_sccm_assetscomputer.json
2018/08/30 10:07:14 [DEBUG] Flag - Zone eur
2018/08/30 10:07:14 [DEBUG] Flag - Dry Run false
2018/08/30 10:07:14 [DEBUG] Loading Config File: E:\Hornbill Connectivity\SCCM connectivityNew/conf_sccm_assetscomputer.json
2018/08/30 10:07:16 [ERROR] Database configuration not set.
2018/08/30 10:07:16 [ERROR] Database configuration not set.
2018/08/30 10:07:16 [ERROR] Database configuration not set.
2018/08/30 10:07:16 [ERROR] Database configuration not set.
2018/08/30 10:07:16 [DEBUG] Updated: 0
2018/08/30 10:07:16 [DEBUG] Updated Skipped: 0
2018/08/30 10:07:16 [DEBUG] Created: 0
2018/08/30 10:07:16 [DEBUG] Created Skipped: 0
2018/08/30 10:07:16 [DEBUG] Time Taken: 1.7091709s
2018/08/30 10:07:16 [DEBUG] ---- XMLMC Database Asset Import Complete ----

Steve G · August 30, 2018

Hi @Lyonel,

Apologies for the late response, I'm currently on holiday so haven't been checking the forum regularly

Looks like you've used the config supplied by @Victor, and within that authentication is set to SQL and not Windows. So if you've not supplied SQL credentials, then that is why the "database config not set" is being returned... Could you change authentication back to Windows and let me know if that works? If not then I'll try to get logged in tomorrow to take a look.

Thanks,

Steve

Lyonel · August 31, 2018

Damn I feel stupid now!!!!

Thanks @Steve G for spotting that!

We are making progress... but I still have problems.

With encryption turned off:

2018/08/31 09:28:04 [DEBUG] ---- XMLMC Database Asset Import Utility V1.5.0 ----
2018/08/31 09:28:04 [DEBUG] Flag - Config File conf_sccm_assetscomputer.json
2018/08/31 09:28:04 [DEBUG] Flag - Zone eur
2018/08/31 09:28:04 [DEBUG] Flag - Dry Run false
2018/08/31 09:28:04 [DEBUG] Loading Config File: E:\Hornbill Connectivity\SCCM connectivityNew/conf_sccm_assetscomputer.json
2018/08/31 09:28:04 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:28:04 [DATABASE] Connection Successful
2018/08/31 09:28:04 [DATABASE] Running database query for Server assets. Please wait...
2018/08/31 09:28:04 [DATABASE] Query for Server assets:SELECT [...]
2018/08/31 09:28:04 [ERROR]  [DATABASE] Database Query Error: driver: bad connection
2018/08/31 09:28:04 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:28:04 [DATABASE] Connection Successful
2018/08/31 09:28:04 [DATABASE] Running database query for Laptop assets. Please wait...
2018/08/31 09:28:04 [DATABASE] Query for Laptop assets:SELECT [...]
2018/08/31 09:28:04 [ERROR]  [DATABASE] Database Query Error: driver: bad connection
2018/08/31 09:28:04 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:28:04 [DATABASE] Connection Successful
2018/08/31 09:28:04 [DATABASE] Running database query for Desktop assets. Please wait...
2018/08/31 09:28:04 [DATABASE] Query for Desktop assets:SELECT [...]
2018/08/31 09:28:04 [ERROR]  [DATABASE] Database Query Error: driver: bad connection
2018/08/31 09:28:04 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:28:04 [DATABASE] Connection Successful
2018/08/31 09:28:04 [DATABASE] Running database query for Virtual Machine assets. Please wait...
2018/08/31 09:28:04 [DATABASE] Query for Virtual Machine assets:SELECT [...]
2018/08/31 09:28:04 [ERROR]  [DATABASE] Database Query Error: driver: bad connection
2018/08/31 09:28:04 [DEBUG] Updated: 0
2018/08/31 09:28:04 [DEBUG] Updated Skipped: 0
2018/08/31 09:28:04 [DEBUG] Created: 0
2018/08/31 09:28:04 [DEBUG] Created Skipped: 0
2018/08/31 09:28:04 [DEBUG] Time Taken: 624.0624ms
2018/08/31 09:28:04 [DEBUG] ---- XMLMC Database Asset Import Complete ----

Encryption turned on:

2018/08/31 09:29:45 [DEBUG] ---- XMLMC Database Asset Import Utility V1.5.0 ----
2018/08/31 09:29:45 [DEBUG] Flag - Config File conf_sccm_assetscomputer.json
2018/08/31 09:29:45 [DEBUG] Flag - Zone eur
2018/08/31 09:29:45 [DEBUG] Flag - Dry Run false
2018/08/31 09:29:45 [DEBUG] Loading Config File: E:\Hornbill Connectivity\SCCM connectivityNew/conf_sccm_assetscomputer.json
2018/08/31 09:29:46 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:29:46 [ERROR]  [DATABASE] [PING] Database Connection Error: Login error: read tcp 6.29.9.38:63179->6.29.9.154:1356: wsarecv: An existing connection was forcibly closed by the remote host.
2018/08/31 09:29:46 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:29:46 [ERROR]  [DATABASE] [PING] Database Connection Error: Login error: read tcp 6.29.9.38:63181->6.29.9.154:1356: wsarecv: An existing connection was forcibly closed by the remote host.
2018/08/31 09:29:46 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:29:46 [ERROR]  [DATABASE] [PING] Database Connection Error: Login error: read tcp 6.29.9.38:63183->6.29.9.154:1356: wsarecv: An existing connection was forcibly closed by the remote host.
2018/08/31 09:29:46 [DEBUG] Connecting to Database Server: myserver\myinstance
2018/08/31 09:29:46 [ERROR]  [DATABASE] [PING] Database Connection Error: Login error: read tcp 6.29.9.38:63185->6.29.9.154:1356: wsarecv: An existing connection was forcibly closed by the remote host.
2018/08/31 09:29:46 [DEBUG] Updated: 0
2018/08/31 09:29:46 [DEBUG] Updated Skipped: 0
2018/08/31 09:29:46 [DEBUG] Created: 0
2018/08/31 09:29:46 [DEBUG] Created Skipped: 0
2018/08/31 09:29:46 [DEBUG] Time Taken: 805.0805ms
2018/08/31 09:29:46 [DEBUG] ---- XMLMC Database Asset Import Complete ----

On thing I notice is the port number used. My config file says 1433 but the log shows 1356?

I changed it in the config file to 1356 but I still get the same error message. I looked onto my SQL server logs and cannot see anything at all.

I then tried again without encryption, on port 1356 => same error message.

Finally, tried without encryption, on port 1433 => get the first error message "driver: bad connection"

Reading online from the error message, it seems to be related to the language used by the tool...

I wish I could help but I am out of ideas at this stage.

I triple checked all the settings on our SQL instance:

port 1433 configured on SQLCLI 11.0

port 1356 configured on SQL Server standard odbc: (for all IPs)

Steve G · September 3, 2018

HI @Lyonel,

Looks like this may be an issue with the SQL driver library that I used in this tool. It appears to have an issue with SSO and hostname resolution in networks pushing IPv6 addresses. See this open issue for more details: https://github.com/denisenkom/go-mssqldb/issues/177

Could you change your config so that the value of "SQL Conf > Server" uses the IP address of the SQL host instead of its hostname, and give that a whirl please?

Thanks,

Steve

Lyonel · September 3, 2018

@Steve G using IP instead of host name works like a charm with v1.5.0

Not ideal, but definitely nothing you can do about it. I do understand that. I will keep an eye on the github issue.

Thanks for everything guys! Works as expected now

Steve G · September 3, 2018

@Lyonel Glad to hear we have a viable workaround

I'll also track the issue, and when I get a spare couple of hours I'll see if I can replace the SQL driver with one that doesn't fall-over when IPv6 is thrown into the mix

Steve

Asset import tool - Windows Authentication not working

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in