{"id":1123,"date":"2020-12-27T20:55:17","date_gmt":"2020-12-27T12:55:17","guid":{"rendered":"https:\/\/www.yohz.com\/blogs\/?p=1123"},"modified":"2020-12-27T20:55:17","modified_gmt":"2020-12-27T12:55:17","slug":"connecting-to-sql-server-via-odbc-using-sql-authentication","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2020\/12\/27\/connecting-to-sql-server-via-odbc-using-sql-authentication\/","title":{"rendered":"Connecting to SQL Server via ODBC using SQL authentication"},"content":{"rendered":"<p>We recently had a user who had his SQL Server hosted in a data center, and needed to extract files from his database.\u00a0 He could connect to the instance remotely, but it took too long to extract the files this way.\u00a0 He tried connecting to the database via another computer in the data center, but received the &#8216;<strong>TCP Provider: An existing connection was forcibly closed by the remote host.<\/strong>&#8216; error.<\/p>\n<p>This suggests a network configuration setting issue.\u00a0 Instead of getting the data center to change the server settings, we instead set up an ODBC connection and used that connection in <a href=\"https:\/\/www.yohz.com\/sbe_details.htm\">SQL Blob Export<\/a>.<\/p>\n<p>Using Windows authentication to connect to the instance via ODBC was easy enough.\u00a0 However, if using SQL Server authentication, the password is not stored in the ODBC settings, and the connection would still fail.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1124\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth01.png\" alt=\"\" width=\"645\" height=\"637\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth01.png 645w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth01-300x296.png 300w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth01-624x616.png 624w\" sizes=\"(max-width: 645px) 100vw, 645px\" \/><\/p>\n<p>In order to connect to the SQL Server instance via ODBC using SQL Server authentication, choose the <strong>Connection string<\/strong> option instead.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1125\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth02.png\" alt=\"\" width=\"426\" height=\"541\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth02.png 426w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth02-236x300.png 236w\" sizes=\"(max-width: 426px) 100vw, 426px\" \/><\/p>\n<p>Click on the <strong>Browse<\/strong> button<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1126\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth03.png\" alt=\"\" width=\"426\" height=\"81\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth03.png 426w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth03-300x57.png 300w\" sizes=\"(max-width: 426px) 100vw, 426px\" \/><\/p>\n<p>and select the ODBC connection to your SQL Server database.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1127\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth04.png\" alt=\"\" width=\"445\" height=\"390\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth04.png 445w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth04-300x263.png 300w\" sizes=\"(max-width: 445px) 100vw, 445px\" \/><\/p>\n<p>The SQL Server ODBC connection manager will then prompt you to enter the password.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1128\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth05.png\" alt=\"\" width=\"355\" height=\"160\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth05.png 355w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth05-300x135.png 300w\" sizes=\"(max-width: 355px) 100vw, 355px\" \/><\/p>\n<p>Enter the password in the provided area and click <strong>OK<\/strong>.\u00a0 The ODBC connection string is then filled out, together with your password.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1131\" src=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth08.png\" alt=\"\" width=\"426\" height=\"230\" srcset=\"https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth08.png 426w, https:\/\/www.yohz.com\/blogs\/wp-content\/uploads\/2020\/12\/sqlauth08-300x162.png 300w\" sizes=\"(max-width: 426px) 100vw, 426px\" \/><\/p>\n<p>Click on the <strong>Connect<\/strong> button.\u00a0 <a href=\"https:\/\/www.yohz.com\/sbe_details.htm\">SQL Blob Export<\/a> should now successfully connect to your SQL Server database using SQL Server authentication.<\/p>\n<p>The same steps apply to our other database products that support SQL Server and ODBC connections.<\/p>\n<p>If you require further assistance, you can post your questions in our <a href=\"https:\/\/www.yohz.com\/support\/\">support forum<\/a>.<\/p>\n<div class=\"fcbkbttn_buttons_block\" id=\"fcbkbttn_left\"><div class=\"fcbkbttn_like \"><fb:like href=\"https:\/\/www.yohz.com\/blogs\/2020\/12\/27\/connecting-to-sql-server-via-odbc-using-sql-authentication\/\" action=\"like\" colorscheme=\"light\" layout=\"button\"  size=\"small\"><\/fb:like><\/div><div class=\"fb-share-button  \" data-href=\"https:\/\/www.yohz.com\/blogs\/2020\/12\/27\/connecting-to-sql-server-via-odbc-using-sql-authentication\/\" data-type=\"button\" data-size=\"small\"><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>We recently had a user who had his SQL Server hosted in a data center, and needed to extract files from his database.\u00a0 He could connect to the instance remotely, but it took too long to extract the files this way.\u00a0 He tried connecting to the database via another computer in the data center, but [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[17,44,11],"tags":[18,129,81,48,87,12],"_links":{"self":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1123"}],"collection":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/comments?post=1123"}],"version-history":[{"count":1,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1123\/revisions"}],"predecessor-version":[{"id":1132,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1123\/revisions\/1132"}],"wp:attachment":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/media?parent=1123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/categories?post=1123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/tags?post=1123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}