SQL Server does not exist or access denied

Question

SQL Server does not exist or access denied

sree-226516

SSCrazy

Points: 2590
More actions
December 18, 2006 at 1:26 pm

#175296

Some of my database users getting following error sometimes when they are generating reports.They have this problem only sometimes and they can see this error in application error log.I don't see anything from database side.Nothing in error log.CPU,Memory and disk activity everything looks normal.
SQL Server does not exist or access denied.

Any help is appreciated.

Thanks,
SR
Thanks,
SR
December 21, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

There's too many things that could be causing this for us to give you a solution. But here's a couple of the big possibilities:

1. Timeout on the network or SQL Server.

2. Check that the database is not set for Auto-Close.

3. They are passing the wrong login and/or password.

4. Your SQL Server has a limit on how many connections it can have and you are exceeding that amount.

What type of report are they doing?

I'm sure there are other possibilities, but this type of error is very difficult to troubleshoot from a distance.

-SQLBill

archana krishnan-380527 SSC Veteran Points: 207 More actions · Answer 2

Hi There,

I have a similar problem out here where my distrinuted queries donot work.Let me try and explain this-

I have 2 servers both linked to each other using linked server. Now my distributed query fails with this error 'SQL server does not exist or Access Dened' only when I connect to the server using TRUSTED SECURITY.

Similarly when I register my SQL server using trusted security in Enterprise manager, and try to view the tables of my Linked server, I get thesam error.

However if I connect using a SQL user erverything works fine.

Here are somethings I have tried -

1. Obviously Ping worrks.

2. Ports are defaulted to 1433

3. In the linked server logins, I have chosen 'BE MADE USING SECURITY CONTEXT' and have specified the SQL login under which my distributed queries work fine. I have done this on both linked servers.

I dont understand from where the Trusted user is being picked up during a Distributed query even though I have specified a SQL login.

I have even tried using a OPENROWSET using my SQL Login credentials (when logged into QA as trusted user). This also fails.

Can someone suggest? I really need to get this working!!

Thanks!

Archana,

Pradeep Kumar-289326 Valued Member Points: 56 More actions · Answer 3

Hi Archana,

Well there would be alot of things we need to take into consideration before finding the root cause for your problem.

First of all have you configured your both Servers to access using Mixed Mode (both SQL Server & Windows Authentication)

You need to cross check the below too..

Problem with our DNS
TCP - IP / Named Pipers
Domain / Network
OLE DB

Btw., you can check out the below KB's for more info...

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

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

Cheers.... - Deeps

archana krishnan-380527 SSC Veteran Points: 207 More actions · Answer 4

Hi Deeps/Pradeep,

Thanks for all this.

Yes, SQL server on both machines are configured in Mixed mode. However I havent been able to check the network aspect of all this since I am not very conversant with that.

Yesterday I had a chance to take look at this issue once more. Here is a brief of what I found -

There are two servers in a domain – A and B.There are linked servers connecting one to the other to facilitate distributed queries both ways.

Distributed queries fail VERY RANDOMLY with the below errors –

SQL server does not exist or access is denied.

Sample Query:

Select * from [A].DBName.dbo.TableName

Scenario

Login into Machine A (which has SQL components).
Make a connection to the B server using SQL Query Analyzer and TRUSTED connection.
Run any distributed query to the A database
You can replicate the error J

However the more interesting point is that, when this error happens (remember I am logged into Machine A ), I go and try the same steps by logging into the B server itself. Things work like a charm and this happens ALWAYS!!!

Another funny thing is, when I use a SQL user who has enough permission (having logged into A and connecting to B using QA) things work fine.

The funniest part is that, the distributed query would have worked fine now, 5 mins later it will fail even though we made ABSOLUTELY no changes. No clue why!!

Since this got a little too confusing, we spoke to MS Database specialist and are planning to raise a MS support request for this.

Any other ideas???

Thanks much!!

Archana

archana krishnan-380527 SSC Veteran Points: 207 More actions · Answer 5

HI,

I dont think anyone can think of an suggestion for this

I forgot to mention that our customer (who actually has this problem) confirms that there are no server side scripts running which scans for anything related to security and removes access.

This was one of my suspects because I have seen places where they deply scripts to continously scan through the machines and deny access to the users who have been given permissions, I guess there are some softwares for that too!!!

Any suggestions welcome!!

Thanks!

Archana

archana krishnan-380527 SSC Veteran Points: 207 More actions · Answer 6

Hi

I thought I should share the resolution of this,

Apparently, SQL server on Server A and Server B had different ports defined in the Client Network Utility. One had 1500 and the other had default 1433.

On adding an alias explicitly on both the servers in Client network Utility, the issye apparently has been resolved now.We hope this is a permanent resolution since the issue has not happened for 4 days now after the change.

Regards

Archana