server problem occurred during deployment of vb.net application with MSSQL db

server problem occurred during deployment of vb.net application with MSSQL db

• LukeRazor

  thx greg for helping me to allocate the problem area. I will go to ADO.NET forum for more help. thx again
  
  

• Martin Fierz

  For you to be able to connect to a remote Express instance, following steps below:

  1. SQL Server Express is started on the remote machine, make sure you can connect to it locally on the Express machine

  2. "allow remote connection" need to be turned on for SQL Server Express via "SQL Server Surface Area Configuration" (for example: check "Local and remote connections" and then check "Allow both TCP/IP and named pipes"

  3. Make sure SQL Server Browser service is started on the SQL Server Express machine.

  4. Turn off Windows Firewall on the SQL Server Express machine (using Control Panel. You can add SQL Express to the exception list of windows firewall but to isolate the problem, let's just turn off the firewall first).

  5. Express may need to be restarted when performing above steps. When all above steps are done, make sure SQL Server client tools are installed on the remote client machine, then try to connect to express from the remote client machine using SQL tools such as osql.exe or sqlcmd.exe

  6. If osql.exe and sqlcmd.exe worked on a remote machine and your application still doesn't work, then there may be issues with your application.

  Let me know if it helps,

  Zhiqiang Feng

  This posting is provided "AS IS" with no warranties, and confers no rights.

  
  
  

• Andres Yepes

  I re-coded datasouce to "(local)" , it did not work for me and i tried "localhost", the same error, maybe i messed up something else..... is there any issue related to upgrading .net 2003 project to 2005 project that could be causing the problem cause my project was developed in vb.net 2003 and converted into 2005 project.

  btw, is there any forum that i can simply post my entire project and db file so that people who r interested in debugging can help more

  
  
  

• cvwilletts

  thx very much for ur help zhiqiang, but i havnt been able to fix my problem.

  interestingly, when i backup the database on programming machine and restore it on client machine using MS SQL Server Mgt Studio Express, it worked and i am able connect to the database and alter it.

  however, when i run the application deployed on client machine previously, it gave same error while trying to communicate to the database.

  Are there any other possibilities

  
  
  

• Sudhir

  You can refer to following article on how to configure windows firewall to allow remote SQL Server connection:

  How to enable SQL Server connectivity on Windows XP Service Pack 2

  http://support.microsoft.com/kb/841251

   

  Below are some commands we use to configure firewall to allow SQL-related traffics when firewall is turned on:

  sc.exe config SharedAccess start= auto

  net start SharedAccess

  netsh firewall add portopening TCP 1433 1433 enable all

  netsh firewall add portopening UDP 1434 1434 enable all

  netsh firewall add portopening TCP 80 80 enable all

  netsh firewall add portopening TCP 135 135 enable all

  netsh firewall add portopening TCP 21 21 enable all

  netsh firewall add allowedprogram "%homedrive%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" MySql enable all

  netsh firewall add allowedprogram "%homedrive%\WINDOWS\system32\msdtc.exe" MSDTC Enable all

  netsh firewall add allowedprogram "%homedrive%\WINDOWS\system32\mmc.exe" MMC Enable all

  netsh firewall add allowedprogram "%homedrive%\program files\microsoft sql server\90\com\replmerg.exe" Replmerg Enable all

  netsh firewall add allowedprogram "%homedrive%\program files\microsoft sql server\90\com\distrib.exe" distrib Enable all

  netsh firewall add allowedprogram "%homedrive%\program files\microsoft sql server\90\com\logread.exe" logread Enable all

  netsh firewall set service REMOTEDESKTOP ENABLE

   

  Zhiqiang Feng

  This posting is provided "AS IS" with no warranties, and confers no rights.

  
  
  

• Marshes

  thx greg for the quick response :)

  I use .net 2005 and SQL server management studio express. I successfully connected to the database after restoring to the client machine.

  the connection string in the app is :"workstation id=SSFFCC;packet size=4096;integrated security=SSPI;data source=SSFFCC;persist security info=False;initial catalog=CYM".  i "drag and drop" all data components in design view (e.g. dataset, dataadapter, sqlcommand).

  So is the application tring to find SSFFCC as workstation id even on different server btw, the client machine's name is "SFlaptop". Could you plz suggest some changes to make this work on client machine

  
  
  

• jesus144

  Yes, since your connection string is hard-coded.  No matter where you deploy it, it will try to connect to SSFFCC.  I'm not familiar with these new .NET data components, but there must be a way to specify "local" as the datasource.  You may want to read the documentation, or post in the ADO.NET forum at http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=45&SiteID=1.

   

  
  

• CTGuy67

  Now I understand your question. ADO.Net connection string can be dynamically constructed very easily during runtime, even you drag/drop all data components to the visual designer. You can always use a local computer name (System.Environment.MachineName) or make the computer name configurable by client user and store it in a config file or registry during runtime. 
  
  

• Easy_Care

  hi zhiqiang, thx very much for being patient, i went find the SQL Express instance name via registry key "HKLM\Software\Microsoft\Microsoft SQL Server\Installed Instances" ,it is neither "SQLExpress" nor "MSSQLSERVER", but "MSSQLSERVER SETUPTESTAPPINS SQLEXPRESS", is there any problem

  by the way, i made this change "data source="(local)\SQLExpress"  , it didnt work either.

  Do you think it might be caused by installing .net 2005 and server express 2005 without uninstall .net2003 and server2000 stuff

  
  
  

• vort3x

  Are you saying you can access the database remotely from another machine, but your app cannot   Wouldn't this point to a problem with the way your app connects to a sql server   Or am I misunderstanding the problem
  

• demercurio

  Did you install SQL Server Express as a named instance with the default instance name of "SQLExpress" If so, change the data source to (local)\SQLExpress, or whatever the instance you specified, the SQL Express instance name can be checked via registry key "HKLM\Software\Microsoft\Microsoft SQL Server\Installed Instances". The new connection string should be something like:

  workstation id=SSFFCC;packet size=4096;integrated security=SSPI;data source=(local)\SQLExpress;persist security info=False;initial catalog=CYM

  The connection string in my previous post will only work with default SQL instance with instance name of MSSQLSERVER.

  If you do use named instance for SQL Express, you need to make sure that all the client machine will have the same SQLExpress instance name, otherwise, your application will break on client machines where Express instance name is not SQLExpress (SQLExpress is the default instance name when you install SQL Express).

  Let me know if it helps,

  Zhiqiang Feng

  This posting is provided "AS IS" with no warranties, and confers no rights.

   

  
  
  

• Dave Williams

  If you're sure your app will always use database on local SQL Server instance resides on client local computer, you can just change your hardcoded connection string to following without any further action, it should work:

  workstation id=SSFFCC;packet size=4096;integrated security=SSPI;data source=(local);persist security info=False;initial catalog=CYM

  Let me know if it helps,

  Zhiqiang Feng

  This posting is provided "AS IS" with no warranties, and confers no rights.

  
  
  

• mtohara

  After restoring the database to the client machine, can you connect to the database outside of the application   i.e. via osql.exe or enterprise manager/sql workbench   If not, then you should double-check the settings in SAC.  If so, then it might have to do with the connection string in the app.
  

• Snickel65

  hi greg, sorry for my bad explanation. I developed a vb.net application that communicates with a local hosted mssql database. now i want to deploy the application together with the database to client machine so that the client machine will run the application independently (the database is no longer hosted at my developing machine, but on the client machine). e.g. an IS company developed a MIS for a small business (which only has one machine). the IS company will install the application on the client machine and deploy the database on the client machine.

  my problem is the deployment of the database, is there any possibility that my coding is causing the problem

  hope it's clear enough for you greg, thx for help

  
  
  
• server problem occurred during deployment of vb.net application with MSSQL db