Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server error 4060 Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2005 1:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:58 PM
Points: 230, Visits: 262
I moved our SQL 2000 server to a new box, installed the same software and then restored the databases from backup. Primary SQL application works with no problem. My issue is that Crystal Reports and MS Query (from Excel) both require the use of a ODBC connection (System) in order to function. When I change the server location (in the ODBC setting) to the new server, the Crystal reports will then allow them to be pointed to the new server and work. New MS Queries will also work.
It's the old queries that are refusing to connect. User gets an error message which says SQL State error: 08004 and the server error 4060 - Server rejected the connection; Access to selected database has been denied. Then a Server login box pops up. If I uncheck the use trusted connection and logon as sa, the query will update properly. Otherwise it bombs with the user name as the login.
If I run the spreadsheet query with my admin logon, it will also update correctly and run.
All users connect via a Citrix Metaframe Server and do not have PC's. All the databases are named the same, only the server name has been changed. The OS's are the same.
Somewhere it would appear that a permission is missing but I can't find it.
Suggestions?
Post #161867
Posted Tuesday, February 15, 2005 3:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:22 AM
Points: 1,062, Visits: 355

It is normal that user login would be all messed up.  To correct the action there are a couple way to fix your problem. 

1. EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'

2. delete all user from the new server and script the user from the old server and execute it on the new server.

Good luck.

 

mom

 


 

 




Post #161889
Posted Tuesday, February 15, 2005 3:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:58 PM
Points: 230, Visits: 262
The primary or front end application creates a username_sql in addition to the normal username login. I don't think I can change one without affecting the other.
Post #161890
Posted Tuesday, February 15, 2005 4:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:22 AM
Points: 1,062, Visits: 355

That should be ok don't you think?

OK here is what you told me.  When you create a login, the application create another one for it's use so that mean for each person there are 2 user id is this correct?




Post #161896
Posted Tuesday, February 15, 2005 4:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:58 PM
Points: 230, Visits: 262
Correct, two user id's for each user except sa.

I believe the normal user id is created through windows login.
Post #161898
Posted Tuesday, February 15, 2005 4:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:22 AM
Points: 1,062, Visits: 355

Bob,

if you do select name from sysuser you would be able to correct all of them.  Also make sure all the logins are created on the server first before you try to correct it. There are ways to copy logins from server a to server B (if it is sql 2000 ) and it will preserve the password if it is a Sql account too.

 

mom




Post #161900
Posted Thursday, April 2, 2009 11:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:21 AM
Points: 16, Visits: 52
Hi

I have just experienced that issue.
Solution: edit the server in the connection string of the SQL query in Excel:

Open your Excel spreadsheet,
Click in the data area (where the data should refresh, e.g. A1)
Menu > Data > Import External Data > Edit Query
click [Cancel] and click [Yes] to "Do you want to continue editing this query in Microsoft Query?"

In Microsoft Query, File > Save the query
eg: Query from Server12345_19_6_253_105.dqy
Edit this .qqy in notepad and change the server IP or Name in the connection string
don't change anything else (this time around)
Then, still in Microsoft Query, File > Open the query
(in a second window)
make sure you retrieve data with the new query (press ! )
Remove the old query by clicking the corner [X]
Quit (the open door symbol)

Back in Excel, Data > refresh data (Although it should be already doing it)

That's it. Enjoy.

Alain



Best Regards,

Alain
Post #689176
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse