{"id":38,"date":"2010-06-06T21:30:34","date_gmt":"2010-06-07T01:30:34","guid":{"rendered":"http:\/\/www.obieta.com\/?p=38"},"modified":"2010-06-06T21:30:34","modified_gmt":"2010-06-07T01:30:34","slug":"sql-server-2008-r2-express-remotely","status":"publish","type":"post","link":"https:\/\/obieta.com\/?p=38","title":{"rendered":"SQL Server 2008 R2 Express remotely"},"content":{"rendered":"<p>If you\u2019re trying to <a id=\"KonaLink0\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><span style=\"color: #000000;\"><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative; border-bottom: 1px solid #000000; background-color: transparent;\">connect<\/span><\/span><\/a> to SQL Server 2008 Express remotely without enable remote connection  first, you may see these <a id=\"KonaLink1\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><span style=\"color: #000000;\"><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative; border-bottom: 1px solid #000000; background-color: transparent;\">error <\/span><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative; border-bottom: 1px solid #000000; background-color: transparent;\">messages<\/span><\/span><\/a><\/p>\n<p><a id=\"KonaLink1\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><\/p>\n<p id=\"preLoadLayer1\" style=\"position: absolute; z-index: 2147482649; top: -32px; left: -18px; display: none;\"><img class=\"preloadImg\" style=\"border: medium none; width: 22px; height: 22px;\" src=\"http:\/\/kona.kontera.com\/javascript\/lib\/imgs\/grey_loader.gif\" alt=\"\" \/><\/p>\n<p><\/a><\/p>\n<p><a id=\"KonaLink1\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><\/a>:<\/p>\n<ul>\n<li><strong>\u201cCannot connect to <em>SQL-Server-Instance-Name<\/em><br \/> An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the  fact that under the default settings SQL Server does not allow remote  connections. (provider: SQL Network Interfaces, error: 28 \u2013 Server  doesn\u2019t support requested protocol) (Microsoft SQL Server)\u201d<\/strong><br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/1.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Server doesn't  support requested protocol\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_1.png\" alt=\"Server doesn't support requested protocol\" width=\"200\" height=\"62\" \/><\/a><\/li>\n<li><strong>\u201cCannot connect to <em>SQL-Server-Instance-Name<\/em><br \/> An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the  fact that under the default settings SQL Server does not allow remote  connections. (provider: SQL Network Interfaces, error: 26 \u2013 Error  Locating Server\/Instance Specified) (Microsoft SQL Server)\u201d<\/strong><br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/2.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Error Locating  Server\/Instance Specified\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_2.png\" alt=\"Error Locating Server\/Instance Specified\" width=\"200\" height=\"62\" \/><\/a><\/li>\n<li><strong>\u201cCannot connect to <em>SQL-Server-Instance-Name<\/em><br \/> Login failed for user \u2018<em>username<\/em>\u2018. (Microsoft SQL Server, Error:  18456)\u201d<\/strong><br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/3.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Login failed for user 'sa'\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_3.png\" alt=\"Login failed for user 'sa'\" width=\"200\" height=\"49\" \/><\/a><\/li>\n<\/ul>\n<p>To enable remote connection on SQL Server 2008 Express, see the step  below:<\/p>\n<ol>\n<li>Start SQL Server <a id=\"KonaLink6\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><span style=\"color: #000000;\"><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative;\">Browser<\/span><\/span><\/a> service if it\u2019s not started yet. SQL Server Browser listens for  incoming requests for Microsoft SQL Server resources and provides  information about SQL Server instances installed on the computer.<\/li>\n<li>Enable TCP\/IP protocol for SQL Server 2008 Express to accept remote  connection.<\/li>\n<li><strong>(Optional)<\/strong> Change Server Authentication to SQL  Server and <a id=\"KonaLink4\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><span style=\"color: #000000;\"><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative; border-bottom: 1px solid #000000; background-color: transparent;\">Windows<\/span><\/span><span id=\"preLoadWrap4\" class=\"preLoadWrap\" style=\"position: relative;\">\n<p id=\"preLoadLayer4\" style=\"position: absolute; z-index: 2147482647; top: -32px; left: -18px; display: none;\"><img class=\"preloadImg\" style=\"border: medium none; width: 22px; height: 22px;\" src=\"http:\/\/kona.kontera.com\/javascript\/lib\/imgs\/grey_loader.gif\" alt=\"\" \/><\/p>\n<p><\/span><\/a><\/p>\n<p><a id=\"KonaLink4\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><\/a> Authentication. By default, SQL  Server 2008 Express allows only Windows Authentication mode so you can  connect to the SQL Server with current user log-on credential. If you  want to specify user for connect to the SQL Server, you have to change  Server Authentication to SQL Server and Windows Authentication.<\/p>\n<\/li>\n<\/ol>\n<p><em><strong>Note:<\/strong><\/em> In SQL Server 2008 Express, there  isn\u2019t <strong>SQL Server Surface Area Configuration<\/strong> so you have  to configure from <strong>SQL Server Configuration Manager<\/strong> instead.<\/p>\n<h3>Step-by-step<\/h3>\n<ol>\n<li>Open SQL Server Configuration <a id=\"KonaLink5\" class=\"kLink\" style=\"text-decoration: underline!important; position: static;\" href=\"http:\/\/www.linglom.com\/2009\/03\/28\/enable-remote-connection-on-sql-server-2008-express\/#\" target=\"undefined\" rel=\"noopener noreferrer\"><span style=\"color: #000000;\"><span class=\"kLink\" style=\"color: #000000!important; font-family: 'Trebuchet MS',Tahoma,Arial; font-weight: 400; font-size: 12px; position: relative; border-bottom: 1px solid #000000; background-color: transparent;\">Manager<\/span><\/span><\/a>.  Click <strong>Start<\/strong> -&gt; <strong>Programs<\/strong> -&gt; <strong>Microsoft  SQL Server 2008<\/strong> -&gt; <strong>Configuration Tools<\/strong> -&gt; <strong>SQL Server Configuration Manager<\/strong>.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/4.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"SQL Server Configuration  Manager\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_4.png\" alt=\"SQL Server Configuration Manager\" width=\"200\" height=\"91\" \/><\/a><\/li>\n<li>On SQL Server Configuration Manager, select <strong>SQL Server  Services<\/strong> on the left window. If the state on SQL Server Browser  is not running, you have to configure and start the service. Otherwise,  you can skip to step 6.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/5.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"SQL Server Browser Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_5.png\" alt=\"SQL Server Browser Service\" width=\"200\" height=\"136\" \/><\/a><\/li>\n<li>Double-click on SQL Server Browser, the Properties window will show  up. Set the account for start <strong>SQL Server Browser Service<\/strong>.  In this example, I set to <strong>Local Service<\/strong> account.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/6.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Set Startup Account\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_6.png\" alt=\"Set Startup Account\" width=\"200\" height=\"224\" \/><\/a><\/li>\n<li>On SQL Server Browser Properties, move to <strong>Service<\/strong> tab and change <strong>Start Mode<\/strong> to <strong>Automatic<\/strong>.  Therefore, the service will be start automatically when the computer  starts. Click <strong>OK<\/strong> to apply changes.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/7.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Set Start Mode to Automatic\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_7.png\" alt=\"Set Start Mode to Automatic\" width=\"200\" height=\"224\" \/><\/a><\/li>\n<li>Back to SQL Server Configuration Manager, right-click on <strong>SQL  Server Bowser<\/strong> on the right window and select <strong>Start<\/strong> to start the service.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/8.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Start SQL Server Browser  Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_8.png\" alt=\"Start SQL Server Browser Service\" width=\"200\" height=\"136\" \/><\/a><\/li>\n<li>On the left window, expand <strong>SQL Server Network Configuration<\/strong> -&gt; <strong>Protocols for SQLEXPRESS<\/strong>. You see that TCP\/IP  protocol status is disabled.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/9.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Protocols for SQL EXPRESS\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_9.png\" alt=\"Protocols for SQL EXPRESS\" width=\"200\" height=\"143\" \/><\/a><\/li>\n<li>Right-click on <strong>TCP\/IP<\/strong> and select <strong>Enable<\/strong> to enable the protocol.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/10.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Enable TCP\/IP protocol\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_10.png\" alt=\"Enable TCP\/IP protocol\" width=\"200\" height=\"143\" \/><\/a><\/li>\n<li>There is a pop-up shown up that you have to restart the SQL Service  to apply changes.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/11.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Need to Restart SQL  Server Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_11.png\" alt=\"Need to Restart SQL Server Service\" width=\"200\" height=\"42\" \/><\/a><\/li>\n<li>On the left window, select <strong>SQL Server Services<\/strong>.  Select <strong>SQL Server (SQLEXPRESS)<\/strong> on the right window  -&gt; click <strong>Restart<\/strong>. The SQL Server service will be  restarted.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/12.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Restart SQL Server Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_12.png\" alt=\"Restart SQL Server Service\" width=\"200\" height=\"143\" \/><\/a><\/li>\n<li>Open Microsoft SQL Server Management Studio and connect to the SQL  Server 2008 Express.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/13.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Open Microsoft  SQL Server Management Studio\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_13.png\" alt=\"Open Microsoft SQL Server Management Studio\" width=\"200\" height=\"154\" \/><\/a><\/li>\n<li>Right-click on the SQL Server Instance and select <strong>Properties<\/strong>.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/14.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Open Server Properties\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_14.png\" alt=\"Open Server Properties\" width=\"200\" height=\"154\" \/><\/a><\/li>\n<li>On Server Properties, select <strong>Security<\/strong> on the left  window. Then, select <strong>SQL Server and Windows Authentication<\/strong> mode.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/15.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Change Authentication to SQL Server and Windows Authentication\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_15.png\" alt=\"Change Authentication to SQL Server and Windows Authentication\" width=\"200\" height=\"180\" \/><\/a><\/li>\n<li>Again, there is a pop-up shown up that you have to restart the SQL  Service to apply changes.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/16.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Need to Restart SQL  Server Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_16.png\" alt=\"Need to Restart SQL Server Service\" width=\"200\" height=\"43\" \/><\/a><\/li>\n<li>Right-click on the SQL Server Instance and select <strong>Restart<\/strong>.<br \/> <a href=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/17.png\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" title=\"Restart SQL Server Service\" src=\"http:\/\/www.linglom.com\/images\/Windows\/Programming\/SQLServer\/Enable-Remote-SQL-2008\/_17.png\" alt=\"Restart SQL Server Service\" width=\"200\" height=\"154\" \/><\/a><\/li>\n<li>That\u2019s it. Now you should be able to connect to the SQL Server 2008  Express remotely.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>If you\u2019re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error messages : \u201cCannot connect to SQL-Server-Instance-Name An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6,1],"tags":[],"_links":{"self":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/38"}],"collection":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=38"}],"version-history":[{"count":0,"href":"https:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/38\/revisions"}],"wp:attachment":[{"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=38"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=38"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=38"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}