I have a job that needs to execute with different account. I figured i need a proxy so i have created one.Now i need to configure a job that runs a store procedure using that proxy account.
View 3 RepliesIn my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedI have a frustrating problem where I am using the Ola Hallengren jobs to backup to a network share. (This isn't something specific to his scripts).
For various reasons the SQL Server account can not be granted access to the share so I thought I would use a proxy account which does have access (this has been fully tested). I am using a CmdExec proxy.
The problem comes now that when I run the job it still thinks access is denied when running the xp_create_subdir command.
When I recreated this problem locally on my machine, as soon as I add the SQL Server account access to the share the backups work, so why isn't the job using the proxy account?
All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select *
from sys.database_permissions
where grantor_principal_id = user_id ('NetworkSERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
As the title suggests we are looking to change the service account of a SQL mirror implementation. I will be using the same account on all 3 servers involved in the configuration.
I know each server requires the account of the other two adding but as this will be the same account I assume this doesn't apply?
Also for mirrored databases already set up would I need to reconfigure the security for each one?
Is there anything I am missing?
Installed sql server 2012 enterprise. Runs with the built in account fine.
I tried entering a domain account to run as the service account from sql configuration it fails with the error "the specified network password is not correct".
I tried from services.msc and entered successfully but when I try to restart it fails that the log in credentials are wrong.
the domain account and password I entered are just fine. What's it I should do or missing?
We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?
View 8 Replies View RelatedSQL 2012 Standard on Windows Server 2012 Standard
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
I have a package on the default instance which runs and completes successfully. When that package is moved to the same SQL server, but a different instance, running under the same service account, it fails. The error is below with some specific stuff removed:
Delete Data from Level 1:Error: Executing the query "-- Variable to capture FileID's
DECLARE @DeleteFil..." failed with the following error: "The DELETE permission was denied on the object '[name removed]', database '[]', schema '[]'.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This makes me think that the package under the non-default instance ends up running under a different security context.
I am working with a client who is rolling out 50+ VM's based of a template we created. This is SQL 2012 CU1 running on Windows Server 2008 R2. Using the default service account the installer has it registers fine and we get the following in the SQL log.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service.
When we change to a domain service account through SQL configuration manager we see the following and cannot connect remote using integrated authentication The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
My understanding is you should and could change service accounts using the SQL Server Configuration Manager and it would set all permissions. Is there something we need to do in addition to get this up and working?
Convert DTS to SSIS |
Document SSIS |
30+ SSIS Tasks |
Real-time SSIS Monitoring |
Quick Starts |
BI Blitz
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedI have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies View RelatedI have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
How can I find out what account sql server is running under?
View 2 Replies View RelatedIf we have a "pool" SQL login, a one that uses SQL Server authentication, and this login is used by different domain account to access SQL Server, is there a way to audit which domain account used that "pool" login to do something on a object in SQL Server? I have to keep this way of accessing SQL Server, so how to create a login for every domain account accesses SQL Server
View 7 Replies View Relatedhey,
i am currently trying to connect to sql server with one of the windows user accounts. I am trying to connect to it via a browser using a dns-less connection. I have put in all the correct user name and password details and it comes back with Login failed for user xxxxxxx.
If i create a user in sql server and put those login details in, it works fine.
Can i use windows users with a dns-less connection???
I wanted to make SQL Server starts up with a certain user account.
But when I entered a user account and password, a message appeared saying something like "user account and Security ID have no mapping......If you choose to proceed, SQL Server might not be able to start up".
Does anybody know how to fix this ?
Thanks in advance.
SqlServer2k is on the domain serverSqlServer2k is on a laptop tooI want to copy a database from the domain to the laptop over the networkusing the copy database wizard.I have done this before with no problem but this time I get thefollowing error:Your SQL Server Service is running under the local system account. Youneed to change your SQL Server Service account to have the rights tocopy files over the network.I went into the properties of MSSQLSERVER under Services andApplications and see no setting described.Where do manage the SQL Server Service?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedHello,
I need to allow SQL server 2005 to open a file in a shared directory. Any assistance or help will be greatly appreciated!
hi ,
i have a problem :)
my sql server 2005 is instaled on windows xp machine ( i have only remote connection on this computer ) , i made mistake and delete my account on sqlserver it was a admin account , the problem now is i cant log on sqlserver enymore becuse sa account is disabled.
What cann i do now ?
Hello,
I'm having trouble setting up PROXY account. I follow the documentation, and Under Credentials, I can only select Operator. Is there a way to select any account under Security?
Your help is appreciated!
can anyone give me step by step instructions as to how I would go about correctly granting the aspnet account access to my sql server 2005 database ??
As far as I can see Ive set it up, but im getting execute permissions errors on stored procedures even though Ive gone into properties and permissions and added the aspnet account with execute permissions.
Hi,
I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.
I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?
If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?
We are debating whether to run the SQL Server service as Local System, a domain user without local admin rights, or a domain user with local admin rights. MSDN recommends local admin rights, but doesn't require them.
I would like to get some idea of how the real world handles this. If you run as a local admin, how do you handle the security implications? And if you run without local admin rights, what gotchas have you run into with extended stored procs, replication, etc?
Thanks,
Jerry Ratner
I have a SQL 2000 (SP3) running on a Windows NT 4.0 (SP6) box used in our test environment. The SQL Server was configured to run under the local system account before I got here. In an effort to standardize things, I tried changing the SQL Service account to run under a designated domain user account purpose built for the job. We use this particular account for all of our new-build servers (which are W2K). This domain account is configured to be a "Power User" on the NT 4.0 Server in question.
Soon after changing things over to run under the new account, all the developers complained that they could no longer connect to the server. I could through QA and EM, but none of the developers could.
The developers are using WebLogic and JDBC drivers for the most part. I wasn't aware that the SQL Server service account affected client connectivity. Was I wrong or is there something else at work here?
Thanks,
hmscott
Hi all,
Please let me know what specific privileges an user account needs to be used as LOG ON AS account for SQL Server Agent in SQL Server 2005.
Does the account needs to me in the domain administrator group?
Thanks,
Hariarul
I have recently installed 2005 Standard and 2005 Reporting Services (on a separate server), today we built a service account for the SQL services in Active Directory. I planned to use SQL Configuration tools to change the account but it fails with the message:
'No mapping between account names and security IDs was done'
I ended up going through and following the manual steps outlined in KB article 283811 - http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811
But I am baffled and concerned as to why it failed.
Any advice?
Future guru in the making.
Hi All hope you can help.I have a SQL 2k Standard Ed. SP3 server that is in mixed securitymode.I have logged into EM with the sa account.Then added a Active Directory group (i.e. DomainDBAdmins) to theSystem Administrators for that server.When I try and modify the SQL server registration in EM to use trustedauthentication instead of sa I get a connection failed. Any ideas?Thanks,
View 3 Replies View Related