We have a Foxpro database on our local machine and a shared sql server database on our remote machine. We have created a remove view that updates or inserts data into the sql server db using the local foxpro data.
What is the recommended way to secure these updates or inserts? We're worried that since our data is not encrypted, it is a security risk.
Thanks in advance.
I'm creating an app that will connect to a central sql server 2005 (hosted on a Win2003 server with Windows Firewall on). I added sqlservr.exe and sqlbrowser.exe to the firewall exceptions to allow for remote connections. Now, an admin I know is telling me that this is not secure and we must configure those 2 exe's to only be accessed by certain IP addresses. I want to make sure the database is very secure, but this sounds like overkill. ??
Questions:
Is this necessary (to limit access to these 2 exe's to certain IP addresses)?
How would I be able to distribute my application to different users if this is so?
How can I make sure that the SQL Server won't get "attacked" or "hacked"?
Mel
I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?
Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.
Is anyone familiar with how to enable remote access to SQL Server 2000? ...and what are you security issues surrounding this?
Any help appreciated.
Adamus
I am connecting through EM to another SQL server for my website, is there a way to limit it so that I only see my database andnot all the 100's of others that are listed.Also, sorry if this sounds dumb but I applied the sp3 sevicepack and I dont know how to verify this, normallly one would go tohelp>about and see it there, there was a sp3 and a sp3a, but afriend told me to only apply the sp3, needless to say it seems slowconnecting and I would like to verify that everything is in workingorder.Thank YouReply to newsgroup as email address is bogus due to spamMichael
View 2 Replies View RelatedI have 2 databases one for datawarehouse and one for reporting.
I created a view in the reporting database joining several tables from datawahourse. The view is meant for a users who uses powerpivot. It will contain a few million of rows.
I set up a role in the reporting database. I assigned select rights to that role on the view.
When the user wants to access the view, he can see the view but a select gives
The server principal "pp_user" is not able to access the database "datawarehouse" under the current security context.
How can I fix this ? I don't want to give the user select rights on tables in the datawarehouse.
Hi all .
I am going to consult you about a security concept .
In my database I have a set of tables (eg. City , Country , ...)
and I have a corresponding View for each Table (eg. V_City , V_Country , ...)
and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.
Thanks in advance.
Hi,I'm
building an asp.net website using web developer express and I have a
SQL 2005 express install on the same machine. Using windows security,
everything works fine for development.Now I need to get it working at the client site, where the web server and SQL 2005 Express are on different machines.I'd
tried using windows authentication but that didn't work, probably
wasn't doing it correctly. I'd like to try SQL Authentication, at least temporarily, since I
can replicate that on my laptop at home easily I think.I created a new SQL login but was really baffled by the screens showing roles and schemas, etc. There's so many roles to choose from and wasn't sure if this new login needed to own any schemas. I
did turn on remote connections and mixed-mode authentication, the
probably now is my asp.net membership provider login page isn't
authenticating correctly, maybe my permissions are wrong on the
aspnet_..... tables for users, roles, profiles, etc. Any advice?thanks,Bruce
I have Developer's Edition of SQL Server installed on my PC. I want my users to run and view my new RS report from their PCs across the network without installing SS on their machines.
We experimented a little bit by asking one user to use these URLs...http://mymachine/reports$myinstance and http://mymachine/reportserver$myinstance) after giving her browse rights in report manager from my PC.
When she uses the first URL, she gets a popup asking for sign in info, but gets nowhere when typing in her windows sign in info. When she uses the second, she gets thru to the report choices but gets an error like...
* An error has occurred during report processing. (rsProcessingAborted)
* Cannot create a connection to data source 'xxxxx'. (rsErrorOpeningConnection)
* For more information about this error navigate to the report server on the local server machine, or enable remote errors
...after choosing my report. xxxxx is the name of the only datasource in the report.
Before we began, we also turned off 'enable anonymous access' and instead clicked 'integrated windows authentication' in IIS on my pc.
My user is on a different domain controller than I am. I asked a colleague who is on the same domain controller to try the URLs and he had no problems running my report from either. He wasnt given any explicit permissions/rights at either the reporting or db level.
Does anyone have a clue?
Hey!
Has anybody here had any issues with the MS03-031 security patch and SQL 2000 on Windows 2003?
We recently installed this patch on one of our test SQL 2000 machines, and the performance of queries running on this machine have gone up (gotten worse) by at least 10X.
We use indexed views and all queries are run with WITH(NOEXPAND) to force the indexed view to be used.
Anyway, after the patch was installed, the queries on the indexed view will ONLY use the clustered index, and not any of the other indexes, causing a Clustered Index Scan.
I removed the patch (well, re-installed SQL) patched it up to SP3a, and performance is back to normal.
Anybody experience anything similar?
Paul
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
To everyone, thanks for reading!
~ Delora
Hello,
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
The remote database is set to Trustworthy.
I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
Thank you for your help on this - Amos.
I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:
An error has occurred during report processing.
Query execution failed for data set 'spName'.
Access to the remote server is denied because the current security context is not trusted.
A few facts ...
The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:
SELECT x.FieldName as OracleField
,y.FieldName as SSfield
FROM OracleLinkedServer..DatabaseName.TableName x
LEFT JOIN Server1.dbo.TableName y
The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;
SELECT 'ABC' as OracleField
,y.FieldName as SSfield
FROM Server1.dbo.TableName y
The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:
Select -- A Custom Datasource
Connection Type: Microsoft SQL Server
Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName
Select -- Credentials stored securly in the report server
Username: UserName
Password: Password
Check -- Use as Windows credentials when connecting the the data source
Check -- Impersonate the authenticated user ...
(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)
Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?
To everyone, thanks for reading!
~ Delora
I have 2 server A and Server B(remote 3rd party) both SQL server 2012. We need to query the data from remote Server B, we are planning to use either view or stored procedure. The query contain multiple joins to fetch data from around 10 tables(minimum 10 tables is there in initial analysis may be more tables will be added in future).If we are going to access the data through VIEW then we are going to write the below query
SELECT * FROM view-name WHERE Column1 ='abc' My first question
Q1. I have a doubt whether this query will load all the data from table joined in the view and then the WHERE condition implement as in the VIEW definition we are not implementing the filter condition.
Q2. Which will has better performance a stored procedure or view.
Q3. How to call a remote stored procedure or View. linked server is not allowed.
Does the 4 part server.DB.Schema.SPorView name will work.
I had search and find about OPENROWSET/OPENQUERY does it works without linked server.
We've got 3 SQL Servers all SQL Server 2012. We have a Master server and 2 production servers (let's call them A and B). The Master has linked servers defined for both A and B in an identical way.
A and B both have the same databases and each has a Stored Proc that gets called by the Master server.
An SQL Agent job executes the stored proc on both A and B.
It works fine against A but not B.
If we execute the stored proc in a query window under our windows account (domainowner) it works, if we use SetUser to pretend to be the SQL Server Agent account (let's call it domainagent) A works and B doesn't.
Executing against B returns the error "Access to the remote server is denied because the current security context is not trusted."
domainagent is a sysadmin on both Server A and B.
Trustworthy is set to ON in both the Master Server Database it's executing from and the remote databases on both A and B.
All 3 databases have the same owner (let's call it domainowner).
Remus (or anyone who can help) -
I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.
In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:
a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.
Thank you for your help on this - Amos.
Hi,
I have a stored procedure servicing a queue (sql 2005).
The stored procedure updates some data in a different server and database.
If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all.
However, if I set the queue to use the same stored procedure, it gives me the following error:
Error 15274: Access to the remote server is denied because the current security context is not trusted.
Any ideas?
Thanks,
Aron
hi,i have an sql database on my server on the world wide web.i can make a connection to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas?
View 2 Replies View RelatedHi,
It appears to work fine on my end however on my clients end, whenever they try to print a report that displays perfectly within the report viewer to their printer by selecting the printer button in the report it never ends up printing.., however if they go into printer setup and change the page range to print from all pages to just maybe one page and then select the print button it seems to print fine..
The printer seems to receive the information as the light flashes but no page kick out when all pages is selected to print..
Any help would be greatly appreciated..
I use from sql server 2008. and c#
what is the best connectionstring?
I don't know if i use Persist Security Info and Integrated Security or not?
And if yes then their value must be true or false?
Hello there I have trying to figure out for days how to enable FullTrust for my Reporting Services security extension.
View 9 Replies View Related
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
hi i want to know what is the differance between
Persist Security Info=False;Integrated Security=Yes;
Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions.
View 4 Replies View RelatedI have Sql Server Express installed on Vista (service pack 2)
I have Visual Studio 2005 with an application that I'm trying to access it with within a WCF service.
The login ID of the service is added to the database.
The database has remote access turned on.
The ID is granted access to all databases within the server.
The thread is being set with WindowsProvider and the services set their thread to WindowsProvider.
The dataserver is set with using Windows Authentication for security.
When I open my connection to the database, though, it reports the typically useless message that the connection is not allowed and that the server may not allow remote connections.
How to I get past this? I've done everything right.
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
Fellow Devs,
I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.
Any ideas why this might be happening? Is there some other configuration?
Is there anybody out there with a MS SQL 2K Security Baseline orSecurity Checklist. Where can I get one????Thanks in advanceDavid
View 1 Replies View Related
Hi;
I am looking for a way to log all security related events for SQL in Windows Security Log. I am trying to use SCOM for monitoring SQL and I am looking at ways to generate alerts in my SCOM Console for specific events in SQL e.g. A table is deleted, user is modified, deleted, etc. Is this possible and if yes how do I achieve the same?
Rgds;
In an environment where there are many initaitors speaking to a central target with frowarders in between, from what i can understand this best policy is to disable encryption on the endpoints, since dialog encryption will be enforced this is all that is really required, is this correct.
If the endpoints used encryption the message would need to be encrypted and decrypted at each forwarder resulting in slower perfromance, where as dialog encryption would only encrypt at the sender and decrypt at the target, so is this the best way to go?
Secondly is it best practice to open a dialog initally and send messages over this dialog for years never ending the conversation? This way the services only have to authenticate eachother once, if there are no reboots etc that is of course.
I would think performance wise sending each message and ending the conversation each time is a much greater overhead ? So would it be best practice to keep dialogs open and keep sending messages ?
Initally when i was learning service broker i thought that one must send a message and end the dialog until the next message, but i think the other way is the best option ?
Is this correct ?
Thanx
Hi
I'm designing a distributed application where I will have SQL Server 2005 distributed databases replicating data to my central hub which is again a SQL Server 2005 database using SQL Service Broker. Data will be sent from the central hub to the distributed sites and vice versa. I need to authenticate the communication and also secure the communication by encrypting the messages. Which security shall I use? Where do I configure the type of security being used? What is the difference between transport security Vs dialogue security - Full security model?
Thanks