| Author |
Topic |
|
shz
Starting Member
7 Posts |
 Posted - 2007-06-15 : 14:19:04
|
| Hi,I am developing some ASP.NET / Sharepoint 2007 application. I receive this error when I try to connect a remote SQL Server 2000. My code is quite simple:SqlConnection conn = new SqlConnection("server=myServer or myIPAddress;database=myDB;UID=myID;Password=myPW;");conn.Open();When I use VS .Net 2003, I get the error: "SQL Server does not exist or access denied."When I try to connect to the server on Sharepoint 2007 Designer, I get the error: "Server error: An error occured while retrieving the list of databases from myServer: the data retrieval service encountered an error during connection to the data source".HOWEVER, I tried many other ways to connect to the server and SUCCEDDED, including:Run the code on Visual Studio 2005;Run the ASP.Net application on a production server - remote to the SQL Server;Ping;ODBC Connectivity;SQL Server client - both SQL Server 2000 and 2005;Visual Studio Server Explorer - both VS .Net 2003 and 2005; etc.NOTE: "myServer" is the only server that causes the problem. Others, both SQL Server 2000 and 2005, are working fine.Anyone gets any idea? Thanks a lot.allanzhang |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-15 : 14:55:24
|
| try adding IntegratedSecurity=SSPI to the connection string_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
shz
Starting Member
7 Posts |
Posted - 2007-06-15 : 15:26:48
|
Thanks, not working though.I have tried basically every thinkable connection string, but to no avail.Well, for now the first problem can be ignored - I deployed the ASP.Net application to the production server and it works, weird though.However, I cannot bypass the second problem with Sharepoint Designer - actually it does not need a connection string at all. quote:
Originally posted by spirit1
try adding IntegratedSecurity=SSPI to the connection string
|
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2007-06-15 : 17:10:56
|
| www.connectionstrings.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-15 : 17:38:15
|
| Add an alias on the client machine that is receiving this error. If the SQL Server client tools are installed on that machine, then you can easily do this with Client Network Utility (2000) or SQL Server Configuration Manager (2005). If you don't have the tools there, then please search the forums for my registry way of doing it. Search on alias and ConnectTo.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-15 : 17:58:20
|
hey tara so far i've read a lot of your answers to questions like these but can you please explain why do you think your answer applies?because it's usually correct and i'm looking at the question and the answer not being able to connect the two.sort of: ok this is the solution. that's great. but why is it a solution?care to educate us underlings?  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-15 : 18:31:36
|
| There are two errors I look for: "SQL Server does not exist or access denied." (2000 and lower error) and "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." (2005 error). Both are very misleading errors. They almost always mean a connection issue. I've never seen the first error be an access issue, ever. And I've been doing this for over 10 years now. It sometimes means the service isn't started though. I always start with an alias when troubleshooting these errors. Typically, the client just isn't able to figure out how to connect to SQL Server. This is common in an environment where you aren't using the default port of 1433.I've now learned of a new way to test connections and that's through this:tcp:ServerName\Instance,PortNumberIf that works in SSMS, QA, or EM, then a simple alias will allow you to connect with the name that you want to.If an alias does not work, then the problem is typically a network-type issue, such as firewalls, DNS, etc...But the key is start with an alias. And don't assume you know what port to use in the alias. You can always check the server for that information either by reading the SQL Server Error Log (it shows you during the startup messages) or via Server Network Utility (2000)/SQL Server Configuration Manager (2005).HTHTara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-15 : 18:42:43
|
| very helpfull! thanx._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
shz
Starting Member
7 Posts |
Posted - 2007-06-18 : 09:25:17
|
| Thanks Tara,However, alias does not work. Actually, if I set an alias, it does not work on VS 2005 - without an alias, it works on VS 2005. |
|
|
|
shz
Starting Member
7 Posts |
Posted - 2007-06-18 : 10:17:32
|
| One more weird thing:I double-checked the settings of the SQL Server - yes, it does use the default port of 1433.The weird thing is: As I said, my code works fine on VS 2005. However, if I add the port number to the connection string as "Server=ipaddress,1433;...", now it does NOT work (on VS 2005). If I change to another server, everything is fine, with or without the port number.Really funny. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-18 : 11:08:10
|
| Your alias was not setup properly if it now doesn't work on VS 2005.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
shz
Starting Member
7 Posts |
Posted - 2007-06-18 : 13:27:57
|
| Well, an "Named Pipes" alias works on VS 2005, but still not on VS 2003 and Sharepoint Designer. |
|
|
|
|
SQL Server does not exist or access denied.