Dreaded "SQL Server does not exist or access denied. " Error

I have built a web application that runs on my local web server against my local SQL server and my development server, but now I'm trying to get it to work on the live server and I'm not getting a connection. Whenever I try to open the connection, I get a.

SQL Server does not exist or access denied.

error.

Here is my connection string...

Public connString as string

Dim SQLServerAsString = "xxx.xx.xx.xxx"

connString = "server=" & SQLServer & ";" _

& "database=DBName;Trusted_Connection=No;user id=xxxID;password=xxxPassword;"

myConnection2 =New SqlClient.SqlConnection(connString)

------------------

I'm starting my connection to run a datareader this way...

Dim strSQLAsString

strSQL = "SELECT ClientID, ClientName " & _

"FROM Client; "

' Create a connection to the table in the SQL database located on

' the remote computer.

Dim myCommand2AsNew SqlClient.SqlCommand(strSQL, myConnection2)

myCommand2.CommandTimeout = 1200

myConnection2.Open() 'HERE IS WHERE THE ERROR OCCURS

Dim myReaderAs SqlClient.SqlDataReader = myCommand2.ExecuteReader(CommandBehavior.CloseConnection)

While myReader.Read()

-------------------

I can communicate with the server with no problem from my local computer. I can ping the server, and I also tested it by creating a system DSN using the login and password in my application, and it finds the database with no problem. I've tried to connect using the SQL Server's server name and the IP address and both return the same result. I've also saw something in another post to set the trusted connection parameter to "yes" and that didn't work either.

I'm developing on a Windows XP Professional and I'm trying to connect to a Windows 2003 Server with SQL Server 2000 installed on the machine.

I did add the database by restoring a backup from my local server onto the live server. I don't think that should matter, but maybe it does. If anyone has any ideas, I would greatly appreciate it.

Try adding this to the end of your connection string and see if it makes a difference:

;Network Library =dbmssocn

This will force a TCP/IP connection instead of a named pipes connection.

(fromBUG: Named pipes do not work when worker process runs under ASPNET account)|||Thanks for the suggestion. Unfortunately that didn't work. Could there be some server firewall setting that is preventing the web user account from accessing the server?|||I would also try adding a port number to the end of the IP address:

Dim SQLServerAsString = "xxx.xx.xx.xxx,1433"

And I would remove Trusted_Connection=No completely. Seehttp://www.connectionstrings.comfor connection string examples. If none of that works, perhapsthe user ID and password are incorrect? Sorry, just grasping atstraws. My last recommendation is to review this KB article:Potential causes of the "SQL Server does not exists or access denied" error message. As you'll see, there are a lot of things to check.|||Thanks for your help. It looks like the problem stemmed from a security hole in Windows 2003 Server, which was shutting down the TCP ports. We applied SP4 and it seems to be working for now. Hopefully that's all it was.

Doubt

I need know what is field equal suid from SQL 7.0 and not exist in
SQL 2000.Use suid.|||What are you trying to do ?|||I wanna mean sid. That column exists in sql7.0 too, but the suid dont exist in 2k now.|||SELECT suid, name, dbname, Admin = sysadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator
FROM master..syslogins WHERE loginname = ' & vfdstrLoginID_App & '

I want the the field equal suid.|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

syslogins.suid --> syslogins.sid
sysdatabases.suid --> sysdatabases.sid
sysremotelogins.suid --> sysremotelogins.sid
sysusers.suid --> sysusers.sid
sysalternates.suid --> sysusers.isaliased
sysalternates.altsuid --> sysusers.isaliased

The related niladic functions also have changed: (the old ones are now "broken")
SUSER_ID () --> SUSER_SID (), returns the user sid given the user name
SUSER_NAME () --> SUSER_SNAME (), returns the user name given the sid

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

SELECT
SUSER_SNAME(syslogins.sid) AS '"user name"',
syslogins.loginname,
syslogins.sid,
syslogins.name,
syslogins.dbname,
syslogins.hasaccess,
syslogins.bulkadmin,
syslogins.dbcreator,
syslogins.diskadmin
FROM
syslogins
INNER JOIN
sysusers
ON
syslogins.sid = sysusers.sid|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:

The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:

For another example see: http://dbforums.com/t561459.html|||syslogins.suid --> syslogins.sid
Binary Hexadecimal

Not is funcionaly in my programation? I want
retorn a value binary, with is the suid ald what retorn
the value sequential of numbers.|||Check out the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;en-us;104829)|||An MSDN link on SUSER_ID: (may be helpful?)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_00mc.asp
As stated in the link: "SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. In SQL Server 7.0, the security identification number (SID) replaces the server user identification number (SUID). SUSER_ID always returns NULL when used in Microsoft SQL Server 2000."|||Okay.
SELECT SUSER_ID ('sa') in SQL7.0
--
1 --> Binary
(1 row(s) affected)

SELECT SUSER_SID ('sa') in SQL2000
--
NULL

(1 row(s) affected)
OR
SELECT SUSER_SID('sa')
--
0x01 -- Hexa
(1 row(s) affected)

I want number sequential and binary per users, understand?|||Q1 I want number sequential and binary per users, understand?

A1 Maybe, (for the most part suid columns were used in joins to return information). Apparently your requirement is different.

Q1 Are you saying the application you are working with requires a 1s and 0s representation of Binary numbers? for example:

User IntRep HexRep Binary
Tom 10 0xA 1010
sa 1 0x1 0001

If you need to use the string '1010' for user Tom instead of 0xA, you could create a function or stored procedure to return 1010 (given a hex representation)?|||What are you trying to do (deja vu) ?

dont insert if record exists

/*if key values exist don't insert new record*/
SELECT

/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@.nStudent, @.nAssignment, 0)
END

FROM tblScores
WHERE gtStudentId = @.nStudent AND assignmentId = @.nAssignment

tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

I'm getting syntax errors when I click check syntax (near keywords insert from and end).

one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

thanksI think the problem is that the INSERT-statement is not a result_expression (see BOL). Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?|||Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?

Its rather complicated but I'll try:

This code is inside a trigger for an enrollment table. If a student joins a class late (after assignements have been assigned) then the trigger creates new records in the scores table giving the new student a 0 for each assignment already assigned to the class. If a student gets unenrolled from the class the scores are maintained in case the student gets reenrolled. If the student gets reenrolled then I want to do a select on the scores table and bypass any scores the student may have had prior to being unenrolled.
INSERT-statement is not a result_expression
How does one do a conditional INSERT?|||IF NOT EXISTS(
SELECT TOP 1 gradeId
FROM tblScores
WHERE
gtStudentId = @.nStudent
AND assignmentId = @.nAssignment)
BEGIN

INSERT INTO tblScores (
gtStudentId,
assignmentId,
score)

SELECT
@.nStudent,
@.nAssignment,
0
END|||I forget which site...but we just did this...Gotta find the code I wrote...

Why would you want to double every access to the database?

Just handle the dup key error...in a calling sproc or the application...

EDIT: It was originally about Contraints...same thing

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1), CHECK(Col1 IN ('Y','N')))
GO

CREATE PROC mySproc99 @.x char(1) AS INSERT INTO myTable99 SELECT @.x RETURN
GO

CREATE PROC mySproc00
AS
DECLARE @.rc int
EXEC @.rc = mySproc99 'Y'
SELECT @.rc, @.@.ERROR
EXEC @.rc = mySproc99 'B'
SELECT @.rc, @.@.ERROR
GO

EXEC mySproc00
GO

DROP PROC mySproc00
DROP PROC mySproc99
DROP TABLE myTable99
GO|||I did not understand very clearly what you are trying to do... is it inserting into your table only those records that _do not already exist_ in that table?

If so, ty this:

Insert into tblScores (gtStudentId, assignmentId, score)

VALUES (@.nStudent, @.nAssignment, 0)

Where (not exists (Select * from tblScores
Where WHERE gtStudentId = @.nStudent
AND assignmentId = @.nAssignment))|||My Point, just attempt the insert...and trap the Error if it fails

Don't make 2 data access attempts to the database