MS SQL and Enterprise Manager Console

[rcampbell]

I have been trying to figure out how to contect my computer at the office to my Plesk Server MS SQL using Enterprise Manager Console. I have had NO Luck. I have tried the Server IP, I have turned all firewalls off, still nothing. I get the following error:

"server does not exist or access denied"

This does not make since, the ISP has given me full access to his firewall and I can access his SQL Machine (not running plesk) with no problems.

Has anyone been able to successfully do this, if so do you have a how-to? If no one has done this can anyone point me in a new direction?

 

[kharrazi]

I am not sure if you installed MS SQL or not but here are some points:

1. Plesk does not come with a real MS SQL server. What you see after installation is the MSDE (testing / limited version of MS SQL) which perhaps you can't connect to it remootly. If you want to have the MS SQL server you have to purchase one which is usually too expensive.

2. Please check if you turned on the Plesk firewall or not? Because that firewall will close almost every incoming port.

Cheers,

 

[rcampbell]

There is no firewall on at this time. I do have MS SQL 2000 installed and running. It is almost like there is another firewall on or that this service can not be done.

Have you or anyone else you know gotten this to work?

 

[jahsh420]

I have an unrelated question. We have a similar setup, our SQL server is a seperate server as our Plesk server. When we create a database on our Plesk server it creates the SQL server on the remote server just fine, but when we login to access the database we can see all the default databases (master, temp, northwind, etc). How can we make it so these aren't visible. This is going to cause more support than necessary for us. Thanks in advance.

 

[jp_andino]

I am having the same concern jahsh420 has, if someone knows about a way to limit the view for clients domains to only see their databases, I appriciatte if someone posted here.

 

[emsol]

Our problem is worse - when a customer clicks on the database manager - it tells them they dont have access to a database that belongs to another customer !

After three or four attempts - sometimes it works.

 

[alldirections]

I could get it to work.
This is what I have done.

I installed MSDE SP3 before installing Plesk. Later I installed Plesk without MSDE.

The download can be found here:
http://www.microsoft.com/downloads/...2C-0488-4E46-AFBF-ACACE5369FA3&displaylang=en

After unpacking the installation file the following command must be run from the command prompt:

setup INSTANCENAME=MSDE_1 SECURITYMODE=SQL SAPWD=yourpassword DISABLENETWORKPROTOCOLS=0

It is important to set DISABLENETWORKPROTOCOLS=0
This will make it possible to connect to the database from another computer.
Please read the information in the sp3readme.htm file you can find in the installation folder.


The installed instance MSDE_1 uses port 1089.
You can find this information by starting the programm:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe


Now you can create a remote connection using the Enterprise Manager. Don't forget to connect to port 1089.


This all works fine when Plesk's firewall is not enabled.
Please add port 1089 in the "pfwmng.js" file used by Plesk to configure the firewall. Don't forget to restart the firewall to make sure the new settings are used.

Hope this helps.

Marcel
All Directions

 

[visupport]

Here is how your can retrieve username/password for MSSQL connection registered in Plesk:

cd %plesk_dir%/mysql/bin
mysql -u admin -p "<Plesk_admin_pwd> " psa

Run the following query in mysql prompt:

select * from misc where param like "%mssql%";

It shows you server,username and password for MSSQL connection.
quit


Test this pass in osql command prompt:

osql -U [username] -P "[password]" -S [server]

If it shows
1>
then you've connected.

Also you can connect to MSSQL using Windows authentication
osql -E
1>
to change sa password (to 12345) type the following:

1> EXEC sp_password NULL,'12345','sa'
2> GO

And finally, to add specified tcp port (1089) to plesk firewall
edit the following array PortsTCP in pfwmng.js like this:
var PortsTCP = new Array ("20",21,22,25,53,80,110,143,443,465,993,995,3389,8443,30000, 1089);
and restart Plesk firewall:
cscript "%plesk_bin%"/pfwmng.js --mode off
cscript "%plesk_bin%"/pfwmng.js --mode on

 

[rcampbell]

I forgot to reply but the service fixed itself after reinstalling the service pack for SQL. Now everything is working correctly

 

[hostmedic]

Originally posted by visupport
Here is how your can retrieve username/password for MSSQL connection registered in Plesk:

cd %plesk_dir%/mysql/bin
mysql -u admin -p "<Plesk_admin_pwd> " psa

Run the following query in mysql prompt:

select * from misc where param like "%mssql%";

It shows you server,username and password for MSSQL connection.
quit

I was hoping that this would allow me to find out the running user/pass for the server...

I am able to login as admin via remote desktop ... but still having issues.

C:\Program Files\SWsoft\Plesk\MySql\bin>mysql -u admin -p "password"
Enter password: ***
ERROR 1045: Access denied for user: 'admin@localhost' (Using password: YES)


Any ideas ?

 

[shwonder]

Please, next time create new thread instead of using existing thread with another problem, not relative to your one.
Ok. About MySql password issue.
You can change Plesk admin's password to new one. To do it follow the next steps. Symbol '>' means that you should enter text in command line.

1. Stop MySql server using "Plesk services control" utility. It is usually displayed in system tray as red gear wheel. Click on it, check "Plesk SQL server" and click on button "Stop" on the right side.

2. Start command line. Start -> Run... -> cmd, press Ok button

3. Change working directory to MySql binary directory. Usually it is relative to Plesk root folder:
>cd "%plesk_dir%\MySql\bin"

4. Start MySql server using following command line:
>mysqld-nt --skip-grant-table

5. Start another command line (see point 2), change directory to MySql binary (see point 3)

6. Start mysql client utility
>mysql

7. Enter following query (change word new_password to any you like. It should be one word, do not use space symbols)

>UPDATE mysql.user SET password=Password('new_password') WHERE user='admin';

Pay attention to the line above. It should be entered correctly. Note: 'password' is a field in table 'user' and there is function named 'password' in MySql. Line above use them both.

8. Exit from mysql
>exit

9. Stop mysqld-nt command. In certain cases it should be done using System Task manager utility. Find process named 'mysqld-nt.exe' and click on button 'End process'.

10. Start MySql and Plesk services using "Plesk services control" utility (see point 1). Services "Plesk SQL Server", "Plesk Management Service" and "Plesk Miscellaneous Service" should be started.

11. Run utility plesksrvclient in command line:
>plesksrvclient

12. If you see red text "Cannot connect to MySql database", it means that password should be changed. Go to next point. If you do not see the text, finish utility.

13. Enter new password in text edit box and press ok button. Close utility.

14. Run utility once again. You should not see red text message.

15. Use new password.

If you find it complex, ask Plesk support to do it for you.

Best regards.

 

[hostmedic]

thanks and apologies

Thanks a bunch - still a bit stuck with this box - other ones working great....


Sorry bout that I thought I had copied & pasted into a new thread - but I had to sign up for the forum account ---

In any event thanks -

could I get your contact info ?
I would like to get your imput on a box I have.

Thanks

Glenn

 

[hostmedic]

sql server enterprise manager...

apologies - posting new thread...

Long long day today.

 

[shortcut]

Got a link to the other thread?

This worked a treat for me, i can log in as the super user using he Enterprise Manager tools that come with MSSQL full server, and can log into the MSDE on a plesk server as the 'sa' super user.

BUT,

Clients can connect to the MSDE server using Enterprise Manager, however cannot view their own database, and basically can't do ANYTHING.

Has anyone found a way to allow clients to upload their database using Enterprise Manager?

I've seen clients connect remotely using MS ACCESS 2003, using \\server.ip.add.ress\databasename as the data source

but cant figure out enterprise manager?

Cheers,
-Sean

 

[shortcut]

Ok,

Managed to get clients access to their databases using Enterprise Manager. I updated to the latest service pack for the Enterprise Manager tools, and this did the trick.

So now..

Clients can log in, and see their databases ok using Enterprise Manager from the full MSSQL tools.

Database on the server is the MSDE.

My new question is...

Has anyone successfully restored a backup .bak database file to the server? it doesnt allow a client to browse their local machine, only the servers filesystem, and if they specify a path to their own httpdocs, it denies permission.

Does anyone know where to allow filesystem read access to a directory for a set client with MSDE?

Cheers!

-Sean

 

[shortcut]

The latest versions of plesk for windows, 7.5.x have relocated the psa database to a mysql server running on port 8306 instead of standard port 3306 mixed up with all the client databases.

This information is in the 8306 database, AS WELL AS the 'admin' password for the standard client database running on port 3306.

cd %plesk_dir%/mysql/bin
mysql -u admin -p "<Plesk_admin_pwd> " --port=8306 psa

Should get you to the mysql prompt for the psa admin database.

From then on the queries:

select * from misc where param like "%mssql%";

and

select * from misc where param like "%mysql%";

will return the info you need!

Cheers,
-Sean