I am wondering is distributor admin account a sql authentication account? How can i use this account to connect to distributor if distributor is on the different server with publisher?
Thanks a lot.
I have a bit of problem I was hoping someone could point me in the right direction. I have a SQL Server
2005 database which leverages both the Membership and Roles APIs. When I recreate
the database for production release, I simply run an sql file using the sqlcmd
utility - no problem. What I need is a way to add a default administrator role,
account and assign this new administrator to the administrator role.Can someone advise on how this is typically handled?
If someone can tell if it is wise change the SA account password after all of your databases have been set up using NT Authentication for login. Also, by using the sa password at login are you providing more security and and who should have access to that password (Your developers or your Administrators?)
Thanks
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 RelatedAm trying to run SQL Server Agent with a service account which is not in the Administrators group. Have done the following -
1. Removed the service account from the Administrators group on the machine
2. Assigned sysadmin privileges to the service account
3. Added it to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER role
4. Through SQL Configuration Manager assigned this account to the SQL Server Agent service
However, this does not start the Agent as a service. What is it that is missing?
Re: SQL Server 2005
Does the xp_cmdshell proxy account need admin-level permissions on the server?
The reason I ask this is because I keep getting "Access is Denied" errors when trying to run this command as a non-admin:
master..xp_cmdshell dtexec 'some package'
The 'some package' has an "execute process task" which calls a batch file on the server.
If the proxy account is NOT a local admin, the "execute process tasks" fails with an "Access is Denied" error.
If the proxy account is a local admin, it executes fine.
We have given "Everyone" FULL CONTROL of all the folders that are affected by the batch file, and it still does not work.
I am out of ideas at this point. It just does not work unless it's an admin.
Are we missing something here?
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies View RelatedI recently enabled log shipping on our production database, and I had initially accepted the default of 72 hours to delete copied logs. Well, i am running out of space quick, and I need to edit it to something like 6 hours.
When I try to disable log shipping in order to recreate it, or if I try to edit the secondary server settings when I am logged in as SA, or my windows account which has the sysadmin role assigned, I get an error that says:
Only members of the sysadmin fixed server role can perform this operation. Error 21089.
I've restarted the sql service, disabled and enabled the permission on my account, but for the life of me, i cannot get this to work!
I'm working on a newly installed windows 2012 R2 server which has SQL Server 2012 and SSMS installed on it. What has been odd is that when I open the "restore database" dialog, in the "Select Backup Devices", "Locate Backup File" dialog, only the immediate folders off of the root of the E: drive are visible. E: is where sql server data and backup files are stored.
The account I am logged in with is supposed to have Enterprise Admin rights. How is can I not see these folders and files? The files are certainly there and are visible to me on that login with windows explorer. I am able to backup via SSMS without issue.
When I look at the folder's permissions everything seems normal. If I add "Everyone" to that folder's permissions the folders and files become visible. The SQL Server service is running on the NT ServiceMSSQLSERVER account, which is normal for my other installations of sql server.
Precedents
Windows 2000 Server
1 SERVERNAME
SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
1 SERVERNAMEINSTANCE
SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
IIS 5.0
SQL Server CE 2.0 (sp3a)
Merge replication previously working fine.
The problem
After migrating MSDE from sp3a to sp4 (in order to fix CE replication bug) I can't sync to MSDE.
I got the following error code: [29045]->[Initializing SQL Server Reconciler has failed.] so I've looked into SQL Server CE log file and I've found the following error message:
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000
The process could not connect to Distributor 'Distributor-name'. 20084
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000
Data source name not found and no default driver specified
I've already tried without success
Uninstalling all SQL Server 2000 (EE) instances (except for the "no instance")
Unistalling and re-install MSDE (sp4)
Re-registering sscerp20.dll.
Rebooting server.
Restarting IIS.
Any ideas?
Sebastian
If you were to do a fresh install it would set permissions on the disk so everything just works.
Now when changing the service account (e.g. to a domain user) use the configuration manager, does it do the same magic (possibly sans if the database data/log files are on another disk)? Or do you need to trawl through the dozens of folders and assign rights manually?
Is it possible to make window authentication using Connection String.
Like
Data Source="Remote Host"; User ID="user1"; Password="Password"
Integrated Security=SSPI'
user1 have rights on SQL server.
Is it possible user2 logged in on different machine can access SQL server.
user2 does not have rights on SQL Server.
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 Related
A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
I did all my best, but no luck so far
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
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 folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
View 2 Replies View RelatedHow to give authentication for Send Mail Task component?
View 4 Replies View RelatedHi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 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
Hello,
(Using win2k, sqlserver2k, framework 1.1)
I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
Any ideas??
Hi all,
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
Thanks.
Hi all,
My work is using a shared application
which accesses a MSSQL 2000 database. To access the application, the
folder on the Windows 2003 Server is shared and users can access the
folder through a shared drive.
For the application to access the
database, it uses an ODBC connection to the MSSQL server which
originally used the SA password.
We have recently switched to using
Windows Integrated Authentication because we believe it offers a
higher level of security. However the only way in which we have been
able to enable this is to add the windows users to the SQL server.
The problem with this is that the
application sets permissions for individual users on what records
they can see within the database. We have found that by adding the
windows users to the SQL Server, they can bypass the permissions the
set by the application by simply using any application that can use
an ODBC connection, such as Enterprise Manager, and see all the
database.
One way around this would be to set up
domains of users with access privileges to the tables which reflect
the permissions set by the application, and configuring a view of the
data so they may only see the records that they have permissions to.
However to do this would require a high administrative cost to ensure
that changes made in the application are reflected in the privileges
of the SQL server.
Instead, is there a way the SQL server
can authenticate that the ODBC connection is coming from the correct
application using Windows Integrated Authentication?
This would allow the applcation to
determine security, and stop users from connecting to the SQL server
using other applications.
Alternatively, can the SQL server,
using Windows Integrated Authentication, also ask the application to
supply a username and password?
Any help with this matter would be
greatly appreciated.
Thanks!
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
View 4 Replies View RelatedHello
Can anyone tell me what is the difference between sql authentication and windows authentication.
Examples of each would be very useful
Many thanks in advance
Steve
Would anyone please help me out here. which of the 2 modes of authentication is better and why??
View 3 Replies View RelatedHi,
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
Thanks
Santhosh
For using different services of SQL SERVER 2005 which is better...
Windows Authentication or SQL Server Authentication?
what are the advantages and disadvantages of both?
After extensive testing I have found that bcp's run almost twice as fast on my dedicated Distributor than on either the Publisher or Subscriber. I was wondering if there is a way to make the bcp's spawned by creating a Snapshot run on the distributor and not the Publisher.
Any comments?
Hi.
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?