February 15, 2005 at 1:58 pm
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?
February 15, 2005 at 3:42 pm
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
February 15, 2005 at 3:48 pm
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.
February 15, 2005 at 4:26 pm
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?
February 15, 2005 at 4:32 pm
Correct, two user id's for each user except sa.
I believe the normal user id is created through windows login.
February 15, 2005 at 4:36 pm
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
April 2, 2009 at 11:10 am
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
Alain
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply