?
Solved

Moving c# programs and SQL to a new server

Posted on 2016-08-31
12
Medium Priority
?
111 Views
Last Modified: 2016-09-06
We are moving our current programs to a new server.  I need to test them before they go live.  This particular program is web based using c#. When I use Visual Studio and step through my code everything works like it should.  When I publish the program and run it I get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections...

I am thinking it is a permissions problem but I am not familiar enough to know where to start.  Can someone help me with this?

BTW- I looked at the 'Connections' for this sql server and the 'Allow remote connections to this server' is checked.
0
Comment
Question by:huerita37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41779011
Can you access the SQL server remotely via ssms?
0
 
LVL 1

Accepted Solution

by:
Danlo earned 1000 total points
ID: 41779016
There are two places you need to set permissions in SQL Server (Management Studio).

First, you need to be sure there is a Login [Server -> Security -> Login] that matches the login the C# applications are trying to connect with.

Second, you need to add a 'User' to the database that the application connects to, using the login account you created above.

[Server -> Database -> Security -> Users]

Make sure the User has at least dbDataReader rights (for Read-only access), and also dbDataWriter rights, for the ability to modify data in the database.

HTH,
Danlo
0
 
LVL 1

Expert Comment

by:Danlo
ID: 41779020
In addition, make sure that the SQL Server instance is set to allow both SQL Server authentication and Windows Authentication, unless the C# app is impersonating a user.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 64

Expert Comment

by:Fernando Soto
ID: 41779100
Did you update the connection string to point to the new server?
0
 
LVL 14

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 1000 total points
ID: 41779266
Going to basics - The first task is that your request must "reach" the server.

If you use SSMS on your workstation (not where the SQL Server service is hosted), can you connect to your SQL Server instance? If this is a new server that IT built, do ask IT to check the firewall. Newer operating systems block traffic and hence it is possible that you are unable to reach the SQL Server itself.

Next, check if accces to the server using TCP/IP is enabled (it is disabled by default). You can check this in the SQL Server Configuration Manager on the server (which is the same place you would go to in order to manage your SQL Server service).

If the above two points check out, then the instance level configurations of allowing remote connections and setting permissions come into play.
0
 

Author Comment

by:huerita37
ID: 41781779
I'm testing the above responses.  I am not at work on Thursdays so I am looking at it now.
0
 

Author Comment

by:huerita37
ID: 41782022
Here is my testing so far:

I am able to access the DB even if I am not on the server itself.  If I run my program locally I can access data on the SQL Server.

There is a login and user that matches what I use to connect to the SQL Server.

I am pointing to the new SQL server because I can select/update to the new server when I am testing locally.
0
 

Author Comment

by:huerita37
ID: 41782108
Is there a permission on the webserver that I need to set?  When I run the program from the new webserver but point to the old SQL server I get the same error.
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41782159
I believe it is the firewall on the webserver which may be blocking outbound traffic.
1
 
LVL 1

Expert Comment

by:Danlo
ID: 41782280
I believe it is the firewall on the webserver which may be blocking outbound traffic.

Nakul Vachhrajani may be correct. here.  Standard SQL Server traffic port is 1433.

From a command line window on your web server, run 'netstat -aon | more' so check if that port is being listened to/for.

If the server is not indicating that it is listening for that port, you may need to enable the TCP/IP protocols inside SQL Server Configuration Manager.

If it IS listening for that port already, it's most likely being blocked by a firewall setting.  Make sure you have SQL port open in Windows Firewall for Domain and/or Private networks.
0
 

Author Comment

by:huerita37
ID: 41786649
I have not been given permissions to get to this server.  I can only access the DB tables.  I have talked to our DB admin about everything that has been said here.  He checked the connection and it was not set to a local connection.

Thank you everyone for all of your help.
0
 

Author Closing Comment

by:huerita37
ID: 41786653
I appreciate all of your help.
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question