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 RepliesHello,
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAINdgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.
Thanks,
Dave
Current: One common SQL login is being used by SQL DBA on all the servers
New Plan: Creating one windows AD group, adding the DBA's to that group and create as a login with sysadmin server access on all the SQL Server boxes
how to achieve this activity. Creating SQL login is fine but how to change the ownership of various objects, jobs to new login on all servers?
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)"
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;
What's the better security to use? Currently I'm always registering using the Windows authentication. When I'm trying to register using SQL authentication I always get "Login failed for user 'sa'" error....
View 1 Replies View RelatedHi there
I got this very weird issue on of our SQL2005 box. For some reason, can't add an NT group. Adding NT user is no issue at all.
It keeps saying:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Login 'EEEEEE-SL-SQL-MAX_QRY-DBOWNER'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Windows NT user or group 'EEEEEE-SL-SQL-MAX_QRY-DBOWNER' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
The SQL Version is SP2 - 9.0.3042. BTW ... I've renamed the actual group name for security purpose.
Thanks
Hi All,
Can we implement a table level (object) security on the groups of the windows NT domain using the integrated windows NT security so that one group has
the permission over one table to update ,select ,delete the information and other group has permission has for some other table.
Thanks
Ajay
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?
Hi all
What i want to do - Execute a Stored Procedure when a user log on that is in aActiveDirectory Group.
I want to create a storedprocedure that will be executed when a windows user log on that is part of a specific ADGroup.
I was able to create the ADGroup and add it to logins. I was able to create the procedure with the ADGroup as owner.
The problem is when the user log on, he is not seen as part of the group that has rights on the DB.
Please help
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
In my architecture I have a Domain Controller with Active Directory (DOMAIN_A), IIS 6.0 (WEBSERVER) and SqlServer 2000
(SQLDBSERVER).
The WEBSERVER has a
.NET application with windows authentication. The .NET application interacts with the
database server. I want to use Integrated Security to pass in
the users login credentials to the database to run any database calls so that I can
audit who is making what calls on the database.
The connection string
I am using for this is as follows:
string connStr =
"Server=SQLDBSERVER;Database=xxx;integrated
security=SSPI";
The problem arises
after I login to the web application (I use the user DOMAIN_ASomeUser where
SomeUser is a user who has permissions set up to make all of the database
calls). After logging in however, when I go to a page that makes a database
call I get the following error:
System.Data.SqlClient.SqlException: Login failed for user
'DOMAIN_AWEBSERVER$'.
It seems that for some
reason, .NET is not passing the login name SomeUser, but instead is passing
SERVERNAME$. Would anyone pls have any ideas how I can implement this. Any guide or references would be much appreciated.
Thanks in advance.
WE have an appl testing the SQL security feature, at some point in time
they drop the BUILTINAdministrators group from the login,
changed the security mode to "Windows NT only", but no users have system admin
server role, after the SQL server was restarted, no one can connect to the server. No one has authority to change the security mode back to "mixed"
(all standard logons will not work)
Any comment ?
The way I can think of to fix the problem:
detach all user databases
run rebuildm.exe
reattach all user databases
recreate the required logins
Any other suggestions/solutions, Thanks
Anthony
Hi,
I am having a problem in applying subscription to a report.
Here is my case:
I have a datasource with 'Windows integrated security ' authentication, yes....I am using windows domain authentication, so login info is not stored in DB but its authenticated from domain. Now the report I want to use subscription on is using this DS. I read it somwehere that I can use rsconfig -e -u -p option to assign user to unattended reports.
I think, this is the unattended report. I tried with rsconfig and then clicked on 'new subscription option'...and i got error (again) saying 'Subscription can not be created because the credentials used to run the report are not stored...or if linked report.....blah blah...'
I want to use subscription to this report but with 'Windows integrated security', is there any way I can do it ?? How can I assign some user to this report without changing authentiation method and use subscription ?
Help me ...I am stuck, this is the last milestone in my project !!!
Thanks,
Prashant
We're using windows server 2012 R2 and sql server 2014 standard with mixed mode authentications. When we connect to other windows server using windows authentication and hit the following error:"the target principal name is incorrect cannot generate sspi context"..is the above err related to mixed mode authentications at installation?
View 3 Replies View RelatedI have a set of reports that read from an Analysis Services cube. Permissions are managed at the cube level since different users should look at the same report but different data. This is why for those reports, the data source has Windows Integrated Security set.
Those same users should be able to subscribe to those reports, the problem is that for subscriptions to work, the data sources must store the credentials. This means the filtered reports are lost since the stored account will show all the reports for all users.
Is there any way of generating "filtered subscriptions"?
Thanks in advance
Dear mems,
I have a problem, and i don't khow how to resolve, pls help me:
My server is Windows XP (not domain),
I work with SQL Server 2005, installed on my server,
I configurate my SQL SERVER connection is "Windows Authentication mode",
I add user "MyComputerguest" to MyServerSecutityLogins to accept connections from local network
I have many databases: Db1, Db2, Db3...
But, I don't know to configurate my SQL Server to achive these:
1) My clients using "SQL Server Managment Studio" connect to Databases Db2, Db3... on my Server, they can expand, modify, add new all Tables, Sp, functions of Db2, Db3.
2) My clients are not allow to access Db1.
2) My clients can add new Databases Db4, Db5, Db6... in the future and they have full permission on every Database which they create without my interfere.
Best regard,
if you run the following script it takes access from the windows admin from getting into sql server through windows auth. The issue is that the files that are attached logging as SA after that are read only. Is there any solution? When you try and switch the file to read write sql server gives an error saying that it cant read the mdf and ldf--gives a windows access error....
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINUsers')
EXEC sp_dropsrvrolemember [BUILTINUsers], sysadmin
DENY CONNECT SQL TO [BUILTINUsers] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BUILTINAdministrators')
EXEC sp_dropsrvrolemember [BUILTINAdministrators], sysadmin
DENY CONNECT SQL TO [BUILTINAdministrators] CASCADE
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITYSYSTEM')
EXEC sp_dropsrvrolemember [NT AUTHORITYSYSTEM], sysadmin
DENY CONNECT SQL TO [NT AUTHORITYSYSTEM] CASCADE
GO
This is for SQL Server 2005
When logged onto a server and connecting using windows authentication (either by server name or localhost) I am getting in fine, but when I connect using the IP I get the following error.
I can log in using SQL Server authentication with the IP.
Hi,
My application is running on one machine using SQL Server Express
in Administrator user.
How can softwre-user can be restricted to edit stored procedures,functions and triggers ?
{using SQL Server Management Studio Express}
Is the better way to write CLR based procedures,functions and triggers ?
Please tell me.
Parminder
Can someone tell me what are the security watchouts there are in running SSIS or the SQL Server Agent? I am having trouble running a job on a package that runs fine through Integration Services. The only difference seems to be that SQL Server Agent is running the job on the schedule.
Does SQL Server Agent need to have certain rights?
Do I need to be part of a certain group besides Admin?
Does the package need to have a particular security for someone to run the job?
I was finally able to get the package set but now I can't schedule the thing to work.
hi i want to know what is the differance between
Persist Security Info=False;Integrated Security=Yes;
Greetings Folks,
We have a SQL Server Reporting Services instance running SP2. I have attempted to add a domain group in a trusted domain to the server to enable regular users to access reports. I have added this group to the instance folder as a SYSTEM USER and home as BROWSER. I am allowing those settings to be inherited down through the folders (reports and data sources) to the reports. My test user in this report receives:
The permissions granted to user 'DOMAINuser' are insufficient for performing this operation.
(log shows HTTP 500)
when the user attempts to use the URL to go directly to the report. If we try to navigate from HOME and walk the folders the user sees a blank HOME page.
I have used other groups with no problem.
Is there something I am missing in setting up the security environment? Any help would be greatly appreciated.
Thanks, Mark
I am using windows authentication to send messages from one server to another.
The user is a domain user that is sysadmin on both servers and db owner of each database.
The errors I see in porfiler are as follows
At target,
An error occurred while receiving data: '10054(error not found)'.
and the following at the initiator:
Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.
For what its worth, the script for my target endpoint is below, and access has been granted to public:
CREATE ENDPOINT [BROKER]
AUTHORIZATION [MYDOMAINmssqlssb]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
I've actually used these users/objects successfully dozens of times on a seperate server with many clients...I am just re-scripting the objects here on this new server. The new remote machine is an image of one of the original machines that was working fine (with new broker GUIDs).
Thanks for any insight.
John
I am trying to connect as follows:
Server: Windows 2003, SQL 2005, on a domain
Client: Windows 2008 Beta, not on any domain
I created an account with the same user name as the domain user on the client machine. And then I logged in as that user and went to Manage Network Password. I entered the correct domain credentials. Verified that this worked for file shares. However, SQL does not appear to be recognizing this and it tells me:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I have verified that this domain account is working properly with SQL when the client is also on the domain.
How can I get this Windows authentication scenario to work where the client is not on the domain and the SQL server is on the domain?
We are seeing login failures for windows accounts. Below is the error message.
Description: In our env most logins are windows accounts. Initially we thought it is an UAC issue and we tried to launch the SSMS using "Run as Administrator". However, we are seeing login failures.
Enviroment:
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
RTM Enterprise Edition (HyperVisor)
Error Message in Error Log :
2015-08-10 22:36:45.290 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:36:45.290 Logon Login failed for user 'domainloginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
2015-08-10 22:41:23.470 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:41:23.470 Logon Login failed for user 'domainloginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
Troubleshooting done:
- Recreated the windows login in sql server. Doesn't work.
- ran sp_valdidatelogins. it doesn't return any rows.
- I belong to sysadmin role and when I say, getting below error message.
xp_logininfo 'domainloginname'
/*
Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 64
Could not obtain information about Windows NT group/user 'domainloginname', error code 0x5.
*/
We tried dropping this account and re-creating the windows account with same permissions but still result is same.It throws same error message. Login failure message !!!
Our system is MS SQL Server v7 and NT 4. We have a stored procedure that exec's xp_cmdshell to run an external program located on the server. When a user who has 'sa' rights runs this stored procedure it works fine. When a 'non-sa' user (via the "BuiltinUsers" NT account) runs it, xp_cmdshell produces the following error:
Msg 50001, Level 1, State 50001
xpsql.c: Error 1385 from LogonUser on line 476
Is there an NT security or SQL Server setting I've overlooked that can be changed to allow non-sa users to xp_cmdshell programs?
n.b. The BuiltinUsers account does already have execute permission on the xp_cmdshell procedure.
Hello all.I'm using ADO to connect to a SQL Server database and run a T-SQLquery.The script template I'm using can be found here:http://groups.google.com/group/micr...56?dmode=sourceWhen I run a VBScript, I get no popups. When I run an HTA, I get thefollowing popup:"This page is accessing a data source on another domain. Do you want toallow this?" [Yes] [No]How can I turn off this warning within the script - without having togo into the Tools...Internet Options...Security...Trusted Sites menu?Any help would be greatly appreciated. Thanks!- Dave
View 3 Replies View RelatedI have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:
SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
Thanks much.
I am running SQL Server 2000 (clustered server) with Windows 2003. For some reason MSDTC is failing on distributed transactions. I can run code that begins and commits a transaction from query analyzer, or run a stored procedure from Query Analyzer with the same code in it and both work great, but if I call the stored procedure from a DTS job, then the job fails with the following error:
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error string: The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. Error source: ...... Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Error source: Microsoft OLE DB Provider for SQL Serve. The step failed.
Any help would be appreciated!
Hi , I am trying to connect to MS Sql server 2000 from Java (1.4.2 /1.5 ). I installed my Sql Server(8.00.382) from the one supplied withVS.NET 2001. When I installed it on my laptop it did not ask me for auser name and password. After install when I re-started my machine Isee the server started up with a green light. Now when I connect to theserver from VS.NET it works fine. This is because VS uses windowsintegrated security. I now need to connect using Java , so I downloadedthe microsoft drivers for SQL2000-JDBC sp3 from the microsoft site. Iadded the jar files to my Java project classpath. I manage to registerthe driver in java :Class dbClass = ClassLoader.getSystemClassLoader().loadClass("com.microsoft.jdbc.sqlserver.SQLServerDriver");DriverManager.registerDriver((Driver) dbClass.newInstance() );Connection conn =DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;_integrated security=SSPI");but cannot seem to get a connection as it gives an SQLException sayingthat it is unable to connect:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Errorestablishing socket.I cant seem to figure it out.Can some one help ??I am a newbie to sqlserver so couldnt quite figure out how to changeadmin password or create a new user with the tools provided with thisversion of sql (SQL Server Desktop Engine).Any help will be appreciated.Ebby
View 3 Replies View Related