SQL Security :: Log Ship Database In Server Instance In One Domain To Database
Jul 2, 2015
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
View 6 Replies
ADVERTISEMENT
Oct 23, 2007
I'm setting up a simple SSRS implementation for a non-profit organization, using two servers hosted at a data center. The first server has SQL Server Standard Edition and Reporting Services installed. I've designed and deployed a number of useful reports on this server.
I was hoping to isolate this first server by installing IIS and SSRS on the second server, have users browse from the Internet to that second server (over SSL, of course), and have all reports served up from databases (and, presumably, the report server database) on the first server.
During the installation of SSRS on the second server, however, I'm being prompted to specify the service account. According to the help text:
"Reporting Services. Service accounts are used to configure a report server database connection. Choose a domain user account if you want to connect to a report server database on a remote SQL Server instance. If you are using a local report server database, you can use a domain user account or Local System to run the service."
I believe I want to configure SSRS to connect to a report server database on a remote SQL Server instance; therefore, it appears that I need to enter a domain user account. The only problem is, neither server belongs to a domain; they are members of a simple two-server workgroup.
Does SSRS, configured to connect to a report server database on a remote SQL Server instance, require a domain? Does what I'm hoping to accomplish require a domain? Creating a two-server domain seems like overkill for this implementation, doesn't it?
I appreciate any comments and suggestions. Thanks!
View 1 Replies
View Related
Jun 26, 2007
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
View 6 Replies
View Related
Jun 14, 2006
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
View 1 Replies
View Related
Jul 30, 2015
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
View 4 Replies
View Related
Jan 13, 2007
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
View 1 Replies
View Related
Jan 13, 2007
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
View 4 Replies
View Related
Jun 19, 2015
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
View 6 Replies
View Related
May 7, 2015
In my procedure I need to pull data from a database which is on other server and domain.
As usual I have used the below query but having troubles with mode of authentication.
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=x.x.x.xsyz;Trusted_Connection=yes;','SELECT top 10 * FROM pcm.stats.visits') AS a
I get the below error:
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"
I understand from the error that the domain and mode of authentication is different.
I use windows auth and we are given sql auth on the remote server which is on different domain.
I was suggested to use 'sp_addlinkserver' to link the remote server and query the data.
I am just curious to know if we can succeed through link server, is there any script to use sql authentication of remote server in my procedure and pull the data.
View 9 Replies
View Related
Mar 28, 2008
I am still trying to figure some last issues before installing sql 2005 standard edition.
vista issues, sql 2005 sql sp2 etc
My question is simple actually, It is about configuring administrative accounts on my computer.
I wish to use configure the administrative account?
I wish to use a third party in deploying sql reporting services
Do I need to set up a local account in report manager?
the article in setting up reporting services in vista
uses domain/instance name or computer name/instance name
how do I do correctly set this up? I will be using july 2007 dvd for
sp2 ( I believe) is their any article explaining the steps in sql sp2
some people seem to have trouble in the restore step.....Thanks
I just want to be prepared....can I install cd's without the use of the internet....
View 1 Replies
View Related
Feb 16, 2007
Hi, I have been creating a web store, which we will be hosting on our Windows Server 2003 system. I am also writing a windows application that will be running on our client systems on the same network/domain. I have no need to allow internet access to the database, just within our domain. Pretty much the windows application will be running and whenever a customer places an order, the program will automatically pop-up the needed information for our credit card processing, and shipping department. So far I have been able to establish a connection to our server by creating a data connection under the server explorer. However, it does not list any databases whatsoever, the web site has been published using Visual Studio 2005 Professional on the server, and is in 100% working order. I have searched and haven't found anything concrete on this, i'm sure its just something simple that im missing. It appears that the server is definetely connected to the database (since the website is fully functional), but it is not listing the information for other systems to access it. If anyone has any ideas, please let me know. Thank you
View 8 Replies
View Related
Jan 22, 2008
After SQL Server 2005 Database Engine is installed by domain administrator, how to give permissions to a regular domain user so that user can control SQL Server Database service?
View 3 Replies
View Related
Jul 15, 2015
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies
View Related
Oct 26, 2015
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 7 Replies
View Related
Oct 2, 2015
I have managed to successfully connect to another SQL Server instance using a linked server.
I can then do my select statement like this: select * from [servernameinstancename].[databasename].[databaseowner].[tablename]
I just wanted to know how I can drop the database on the other instance?I have tried doing: DROP DATABASE [servernameinstancename].[databasename].
View 3 Replies
View Related
Apr 1, 2002
Hi,
We are using VB as frontend and SQL Server 7.0 as backend applications in our company. The users are using 'User DSN' to connect the databse with NT logged in user security. User can connect the database if he is having the permissions on the database. We are not using SQL Server authontication like 'sa' user. Till here it is working fine. Our problem is, We want to avoid the users to connect the database using SQL Server Enterprise Manager. We have the SQL Server Enterprise Manager software installed on some machines. because some times we may need that. Right now the users (If he is having the permissions on the database) can directly open the database using Enterprise Manager and they can manipulate the data directly.
We need only specific users like The users who are in System Administrators group has to access the database using Enterprise Manager.
Please help on this.
Thanks,
Arun.
View 2 Replies
View Related
Jul 20, 2015
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
View 5 Replies
View Related
Mar 7, 2008
Can I know about this ? I prefer to apply locks on database level and also to row level.
Is it possible or advicable ?
View 4 Replies
View Related
Feb 5, 2008
In my machine , we were used befour sql server2000 and now we are using sql server 2005 ,
So, I have both 2000 and 2005 installed on my machine ,
on my machine i am working on Custome Paging and one function ROW_NUMBER() is not working ,because of i think i am still working on 2000 instance on locally,
Can any one know how can we create an instance of sqlserver 2005 database and work with that?
I want to work with sql server 2005.
View 1 Replies
View Related
Aug 19, 2015
I have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often
- the last one is dated January 5, 2015. I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
View 3 Replies
View Related
Jun 25, 2014
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
View 3 Replies
View Related
Oct 18, 2006
If I create a database in SQL Express and then copy the MDF/LDF files and attach them to a machine running SQL Server 2005 Standard - will this new database have SQL Express limitations?
Are there good reasons not to do this and create the database in SQL Server Standard using scripts generated from the SQL Express instance instead?
Basically, I'm setting up a merge replication between Standard and SQL Express for a common database and I figure attaching a copy of the database created in SQL Express is the fastest way to get this started.
View 3 Replies
View Related
Nov 27, 2006
Hi
I'm going to put my database in setup file that I've been ready before.
My Software could be use from database after installing in every system , but other users won't be able to access to data or structure of database , even with adding of database to SQLServe Enterprise Manager .
How Should I can do about this ?
Tanks very much
View 3 Replies
View Related
May 22, 2015
We can restrict users from accessing the databases from the security. But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible?
View 13 Replies
View Related
Apr 26, 2007
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
View 3 Replies
View Related
Jan 30, 2008
I am using the following C# code to establish a SQL connect to a SQL database file:
// connection string
// attach a SQL database file to a local SQL server express instance
string _connectionString = @"Server=.SQLExpress; AttachDbFilename=C:BalanceDatabase_1.mdf; Trusted_Connection=Yes; User Instance=True";
// using System.Data.SqlClient;
SqlConnection _sqlConnection = new SqlConnection(_connectionString);
// open the connection
_sqlConnection.Open();
// do something
// close the connection
_sqlConnection.Close();
So far, the connection works fine.
However, next, I want to copy the database file to another folder. So the following codes:
// source database file name
string sourceDatabaseFileName = @"C:BalanceDatabase_1.mdf";
// target database file name
string targetDatabaseFileName = @"D:BalanceDatabase_1.mdf";
// copy database file
System.IO.File.Copy(sourceDatabaseFileName, targetDatabaseFileName, true);
Then the program came with runtime exception: "IOException was unhandled: The process cannot access the file 'C:BalanceDatabase_1.mdf' because it is being used by another process."
Is it because the database file was sill attached to the local SQL Server express instance? What can I do to bypass this problem? Detach the database file? or dispose the local SQL Server express instance?
Many thanks indeed!
View 9 Replies
View Related
Dec 28, 2007
How do I add an Importer as a user in my sql server database security folder.
Regards
Karen
View 1 Replies
View Related
Feb 17, 2015
I have a database used in SSRS in which I get a backup daily and restore it to database "DATA"
The issue I come across is SSRS has a datareader user(ssrsuser) for access to the "report store" web where users get reports
access gets erased on each restore. Can I add something to my job to restore this user after each restore or keep the settings?
declare @Bakpath varchar(80)
set @bakpath = 'C:EDataExtractedDataextract.bak'
Use Master
Alter Database [Data]
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [Data] FROM DISK = @bakpath --location of .bak file
WITH REPLACE
GO
View 5 Replies
View Related
Jun 9, 2015
We are unable to connect with Client SQL database server using VPN connection, even we are able connect with their intranet site.
We tried to connect using IP address, but no success. After that tried to ping that server with server name and IP address using command prompt, but got request time out error.
View 5 Replies
View Related
May 6, 2015
I have 2 SQL 2008 R2 Servers which are always running (on is Prod/one is Test).
is there a way to sync Prod to Test WITHOUT using the .bak and overriding Test's Security settings?
View 6 Replies
View Related
May 23, 2008
I have a database that was migrated from a SQL Server 2000 installation. The database was a remote server (call it ServerA) from the Reporting Services install (call that one ServerB). Both of the original servers can effectively be called dead at this point, and I want to redeploy both to a SQL Server 2005 installation (ServerC). RS is already installed on the new box, but is not initialized to any database. The SQL Server 2000 based database (from ServerA) has been restored and upgraded to 2005. The keys from the original RS host box (ServerB) have been successfully restored to the new server (ServerC). Unfortunately, the new server still sees the configuration of ServerB in the database, and thinks that I'm trying to do a scale out deployment. How can I force ServerB to be kicked out of the installation so that ServerC can properly take over?
TIA,
View 1 Replies
View Related
Jan 12, 2006
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
View 1 Replies
View Related
Jun 11, 2015
We have a production server "prod" which has a user say 'test' that lets the users connect to the application and a logon trigger which stops them from connecting to the server through SSMS. I log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.
Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.
View 9 Replies
View Related
