Hot answers tagged

23

You have to configure the following two files pg_hba.conf host all all 0.0.0.0/0 md5 postgresql.conf listen_addresses='*' You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/ If it's not then add a rule to your iptables: iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT 0/0: If you want anybody to access it. ...


16

Please don't use the UI for this. It's a confusing mess. It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created: USE your_db; GO CREATE USER username FROM LOGIN username; GO GRANT SELECT ON dbo.MyViewName TO username; GO EDIT ...


12

For SQL Server 2008 and up, you can disable promotions for the linked server. It's on the Advanced property tab, or you can script it like: EXEC master.dbo.sp_serveroption @server=N'YourLinkedServer', @optname=N'remote proc transaction promotion', @optvalue=N'false' For SQL Server 2005, you can add an OLE DB linked server. That allows you ...


9

It's not really about it being a bad idea in general, more that there are very few instances where you really need to expose your database to a public interface. Much of security is about reducing the number of attack surfaces you expose. If your application does not absolutely have to expose your database to the public then you can hide it away behind a ...


8

This issue is now resolved. It was a network problem, and the SQL box was using a 100 MB/s NIC card, instead of a 10 GB/s NIC card... A network configuration change to use the correct network card has fixed the problem. Now we are getting similar performance for all queries from the Production SQL box and from other boxes on the network. Thanks everyone ...


8

You can use hostname in grant statements as well, as you are using shared ip then you can limit it with hostname specific grant such as below grant all on db.* to 'user'@'hostname' identified by 'password'; To allow from all IP's/hosts grant all on db.* to 'user'@'%' identified by 'password'; For localhost only grant all on db.* to 'user'@'localhost' ...


7

This line: host all all 192.168.0.0/24 md5 will let through connections from IPs matching 192.168.0.X where X is any byte. The IP address of your error message being 192.168.108.161, it does not match this pattern because 108 is not 0. To enable addresses like 192.168.X.Y, you'd need a /16 instead of /24 meaning that ...


7

From xp_cmdshell (Transact-SQL) in the product documentation: The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential ...


6

create login YourTpvLogin with password = 'enter new password here' go use SomeDb go create user YourTpvUser for login YourTpvLogin go grant select on YourView to YourTpvUser go You can test this out by doing the following: execute as user = 'YourTpvUser' go select * from YourView revert go


6

Remote file access occurs over SMB. Remote backups are just another form of remote file access. See File and Sharing Protocol Stack. If you use SMB over TCP directly (ie. disable NetBIOS) then the port used is 445, both UDP and TCP. If you use NetBIOS then it requires 137 both UDP and TCP, 138 UDP and 139 TCP. See Directly Hosting SMB over TCP/IP: ...


5

I had the same error, trying to debug locally some application that connects to remote DBs. When using php < 5.3, everything worked. When using php 5.3 or greater, error shows up. After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user. All databases i use have new password length (41). I changed the ...


5

I suppose the simplest way is to let the tester use ssh to login to your server. Then the tester's execution environment is whatever your server exposes through the shell. (I presume that would include psql.) As far as I know, all modern Unix derivatives install ssh by default. That would include Linux, Solaris, OS/X (Apple), and BSD. I believe that Windows ...


5

Your problem is definitely network related, based on your info. As such, it has to be dealt with with network professionals (I am not the one). Things that might help: Faster NIC cards (on SQL server). Adding of allocated/specific NIC card/subnet between the servers (web-server and SQL Server). Is the web-server in the same sub-net as the SQL server? Are ...


5

Sure, in SQL Server at least you can set up a linked server to other SQL Servers, Oracle, MySQL etc. and pull data from them. There are other approaches as well (such as OPENQUERY). An example would be: SELECT * INTO dbo.localtable FROM OtherServer.database.dbo.TableName; In Oracle I think the equivalent is database link but I confess that I have never ...


5

The recommended way to do this is to set up a VPN between the two sites. The steps to do this vary depending on the particular firewall/router being used. If a VPN cannot be used, the following information is provided for how to set up a connection with port mapping. If port-mapping is used, it should only be used in conjunction with additional firewall ...


4

Regarding the password, you have two choices: An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer ...


4

If the only difference is local vs remote, then the problem has to be your round trip times. The actual internal query execution is not aware of the type of connection used. However I also wonder if you are also running your dev system on a different database. In that case, caching could be very different and could also make a significant difference. I ...


4

The explanation here is a straightforward one: You can't bind a listening socket to an IP address that your machine's IP stack isn't aware of. In AWS (whether EC2 Classic or EC2/VPC) as in many network environments these days, your external IP is not configured in your machine -- it's mapped through an external device (router, firewall) to your machine's ...


3

Since you did this changes to the MySQL side, your only other option is to downgrade PHP. Other links support this: StackOverflow : MySQL PHP incompatibility ServerFault : mysql_connect(): The server requested authentication method unknown to the client [mysql_old_password] in Another Blog : http://datatables.net/forums/discussion/12188/notice-array-to-...


3

You appear to be using the phrase "temporary table" to describe something that is not, in fact a true "temporary table." I have created a temporary table (base on DB1 and federated on DB2) This is impossible, because a temporary table cannot be federated in MySQL. I am going to assume that you are using the word "temporary" to mean some kind of work ...


3

You should not put any faith in triggers when it comes to federated tables. You are better off creating a temporary trigger on the real MyISAM table on the source side. Picture this: On Server S1 - T1 is MyISAM table - T2 is FEDERATED table to T1 over on S2 Do the following: Create the Trigger on T1 to insert a row into T2 Insert Data into T1 Drop the ...


3

In Oracle, you can use a database link object to do it. CREATE TABLE localtable as (SELECT * FROM remote_table@link_name)


3

Agree with first responder. The only way to do what you want, and not 100% sure it is possible, would be to have very high level permissions on all servers, or have special permission settings. Any system admin would be justified in asking for any order to set something like this up in writing. The same goes for the DBAs for the DBs you want to run "sql ...


3

There are 3 methods to set this up METHOD #1 : MySQL Replication Setup MySQL Replication where the Slave has this option replicate_do_table=mydb.mytable Then, any DML (INSERT, UPDATE, DELETE) or DDL (ALTER TABLE) you execute will go immediately to serverB. This makes Method #1 is the fastest and most granular approach. METHOD #2 : Copying the table to ...


3

At initial reading it sounds like you are experiencing some network latency issues. Have you looked at some of the Network Perfmon counters? Those may give you some indication of what is going on with the network. Quote from What Perfmon counters should I monitor and what each of them mean? NETWORK IO To measure network I/O, you can use the ...


3

What about piping a gzipped mysql dump over ssh.


3

I really had to think about this one. Error 1442 affecting GRANT commands ? Note the MySQL Documentation Error: 1442 SQLSTATE: HY000 (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG) Message: Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. If you are using managed hosting,...


3

basic things to check: new firewall inbound rule inside the VM enabling sqlservr.exe. You can make it as restrictive as you want but start with allowing all inbound connections first to verify it works then lockdown one thing at a time new firewall inbound rule inside the VM enabling sqlbrowser.exe. Needed if running named instance. new endpoint for the ...


3

There are two common ways to do this: Easy way - with "normal" database backups - when you run the BACKUP DATABASE command, the SQL Server service on the remote box needs to write the backup to a file. The service needs to be able to write to the destination path. To do this: Set up a shared folder on your local machine Give the SQL Server service ...


3

(self-answering in case someone else has these same symptoms!) Then I stumbled upond Shubham Dipt's post on "PostgreSQL on EC2 (Ubuntu) in AWS", making me aware of the "Security group" layer AWS maintains. It's easy to specify a new one via the AWS console, specifically adding Custom TCP: 5432, Source: Anywhere and SSH TCP:22, Source: Anywhere rules for ...


Only top voted, non community-wiki answers of a minimum length are eligible