SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server error 4060


Server error 4060

Author
Message
Bob Hoffman-209065
Bob Hoffman-209065
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 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?
Sopheap Suy
Sopheap Suy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2033 Visits: 357

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






Bob Hoffman-209065
Bob Hoffman-209065
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 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.
Sopheap Suy
Sopheap Suy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2033 Visits: 357

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?





Bob Hoffman-209065
Bob Hoffman-209065
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 262
Correct, two user id's for each user except sa.

I believe the normal user id is created through windows login.
Sopheap Suy
Sopheap Suy
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2033 Visits: 357

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





A Plockyn
A Plockyn
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 93
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search