SQL Restore Problem

[AKPolarBear]

After restoring our Macola Programs and SQL servers to new servers and getting those up an running, I am finding another issue in using Crystal and MS Query out of Excel.
After I change the ODBC used by those programs to point at the new server, Crystal will allow me to update the reports and work. MS Query however will only work on either new queries or old ones and only if I change the login when it asks for it to sa. It will not work any of the prior ones built by the user and their login.
I am getting SQL errors that point to a login/connection problem. The errors are SQLSate 08004 and SQL Server error 4060.
It is my understanding that this is somewhat a common problem when doing restores to the SQL databases.
What I can't find, is how to correct the problem. There may be a server to server copy function for the logins that would restore the permission and password.

Anybody have any ideas what is going on?

 

[kscheeler]

First, I typically set up a group on the network for Macola and then put the users who need access to the Macola tables via crystal, QA or other outside tools in the group. I then give that group access to my databases in Enterprise Manager. Usually I only give rights to datareader role and then would set up more of an admin group that could also write back depending on the type of script. I would then set all of my odbc connections to windows authentication vs. sql authentication.

If you set up specific users in your original sql server, you will need to duplicate that in the new sql server. There is a copy server wizard which will copy basically everything from one server to another as well.

I tested this in Excel similar to what you are describing and changed the password for my user after setting up the excel sheet. When I went back in and tested it again, it prompted my for my password again (since it had changed). After I gave it the new password, it ran fine and I only had to do it once.

Kevin Scheeler

 

[crystalreporting]

Do the new servers have the same name? Crystal shouldn't be a problem via ODBC, but I have seen where queries developed (and saved) out of MSQuery will not work after you've moved or renamed servers, even using the exact same DSN name. I suspect the UNC is embedded within the saved qry, but I never was able to correct it - instead, I simply recreated the queries.

Peter Shirley

http://www.linkcorp.com

 

[AKPolarBear]

Sorry the split response; my browser did not show Peters comments at first.

Kevin:

User group? Now there is a concept that neither of our two past VAR's never mentioned! We don't write back to the database through Excel or Crystal. We have modified it a few times through Access during the initial setup but were otherwise trained not to do that due to the fact that some fields need to be "validated" through the Macola process. I might have corrected a corruption through Ent. Manager once so write back is not a needed or desired feature for us.

I have never set up users directly in the SQL server. I believe all user logon get setup automatically with the windows logon.

None of the logons have specific rights to datareader in either of the old or new servers.

The ODBC connections in question is set to windows authentication and not sql authentication. They are set to connect to the SQL server with the sa login. The sa login password is different on both servers. Would changing the new one to the old correct the problem?

When the user uses Crystal that is pointed to the new server, a logon box appears. The server name is the ODBC connection, the database is correct and the user name is dbo. Does this sound correct other then it doesn't work?

When changing the ODBC connection from a user logon to the new server, the connection test fails. Same procedure as admin passes.

In looking at the copy function in Ent. Manager, one part says to copy the master for the logins and the next paragraph says you can't copy the master.

If I copy the master from old > new, does it matter that I don't have all the same databases on the new server that are on the old? I took the opportunity to eliminate some unused Macola companies when setting up the new server.

Peter:

The servers have different names and IP address. I have some users that have some extensive queries built and they won't be real happy. But, I have been saying that would be the worst case scenario during this move.

 

[kscheeler]

If you have Macola working on the new server, I would be hesitant to run the copy server wizard in case it copied more than I wanted it to into your now live server.

The reason windows authentication worked was because someone had used SA as the login on all of the computers. You could set the new password back to the old one and that might solve a few problems but I would get away from using SA as the authentication on any of the workstations.

Remember, SA is GOD in SQL. Someone logged in as SA can do ANYTHING they want in SQL; delete databases, delete users, change passwords etc.. Though most users don't even know that or wouldn't know how to use it if they did it should be something that's tightly controlled like being an administrator.

Kevin Scheeler

 

[AKPolarBear]

So how do I correct my problem?

Should I set up the user group and make everyone a member of that?

Just had a little 4.8 earthquake while writing this... I hope that's not a sign from the SQL gods!

Or should I assign a new password to each user SQL login and would I do that both the normal one and the Macola one?
I suspect not.

 

[AKPolarBear]

Update: I read tech doc 01.210.630 about SQL access rights and roles. In our old set up neither of those choices were set up that way. The db_datareader was not checked.

Using my test user, I did check that option in the non SQL logon and tried to run a Crystal Report as that user. No change. It asked for the user name and password which didn't work. I changed the password through Visual manager and tried it again with the same results.

As a side note, a F9 user reported that a box popped up asking for a logon and password. His normal progression password worked without problem.

This is frustrating.

 

[NEmacGuy]

For you Excel Issue the following may work :

1.) Open a spreadsheet that does not work
2.) Press Alt-F11
3.) Press Ctl-G
4.) Copy the following text into the Immediate Window >>>>>>

< P >
Sheet1.QueryTables(1).Connection =Replace(Sheet1.QueryTables(1).Connection,"OLDSERVERNAME.1433","NEWSERVERNAME.1433")


<<<<<<<<<
5.) Press Enter after you paste in the above command
5.) This Should fix the Issue.
6.) Save the spreadsheet and then try running the query.

CHANGE THE SERVERNAMES ABOVE TO THE APPROPRIATE NAMES!