hey,
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 noticed when I restore a master database to a server other then the one which created the backup of master, SQL Server contains the following three local security groups that were defined on the source server. The problem is these groups are "local" and do not apply to the server where master was restored.
ServerNameSQLServer2005MSFTEUser$ServerName$InstanceName
ServerNameSQLServer2005MSSQLUser$ServerName$InstanceName
ServerNameSQLServer2005SQLAgentUser$ServerName$InstanceName
For example, if you have a default SQL Server instance named MARKETING_TEST the security folder will contain the following three entries.
MARKETING_TESTSQLServer2005MSFTEUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005MSSQLUser$MARKETING_TEST$MSSQLSERVER
MARKETING_TESTSQLServer2005SQLAgentUser$MARKETING_TEST$MSSQLSERVER
If you then backup the master database on an instance named MARKETING_PROD and restore it to MARKETING_TEST, the security folder on MARKETING _TEST will now contain the following three entries.
MARKETING_PRODSQLServer2005MSFTEUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005MSSQLUser$MARKETING_PROD$MSSQLSERVER
MARKETING_PRODSQLServer2005SQLAgentUser$MARKETING_PROD$MSSQLSERVER
These entries would be invalid because no such server exists and therefor no such local groups exists. There appears to be no Microsoft documentation explaining how to handle these groups when restoring master from one server to another. My assumption is that whenever restoring master to another server you must drop these three groups and add the correct corresponding groups along with the appropriate permissions. I don't understand why SQL Server would not rebuild this information for you during a restore.
Any explanations?
Dave
If 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 RelatedI have a scheduled job to perform exports of SQL databases to a file share. The file share is setup so everyone can write to it but only the AD account 'SQL.Bkup' can delete. I have setup SQL Server Agent to use this ID when performing backups but the backups fail each time when trying to delete old backup versions.
I have logged into this id (SQL.Bkup) and can delete files on that share without a problem.
Is there some thing I need to setup differently to get the backup process tol run with the correct authority?
Thanks,
Ken Nicholson
We have a SQL database on a non standard instance. If I open SQL server manager and connect to that database using integrated Windows Authentication I can basically do whatever I want with that database.However, if I try to connect to that database remotely (or even from the sql server) using a test connection UDL it only works if I use Windows integrated security. If I specify my account details specifically (or any other account which has access) it doesn't want to connect.
View 13 Replies View RelatedI am no DBA, but this is my task.I have an SQL Server 2000 Database that has an "SQL Account" that hasexecute permission on all Stored procedures. it is what was used bythe company. This one account is used by "all workstations".I want to fix this and use Windows Accounts, and get rid of that SQLAccount. How do I go about adding that Windows Account permission toall the Stored Procedures?What I want to do is to just add several windows account then go aboutremoving the permission where necessary on an account by accountbasis.Any suggestions would be greatly appreciated!
View 2 Replies View RelatedDeal All:
I have got a strange problem on runing SSIS package, please help me.
The package contains a Script Task which function is downloading files from a SFTP server with using psftp command line application. It will run successfully with using dtutil.exe and as a job with using a ssis execute proxy(domian account as credential), but fail runing the package as a job with using a ssis execute proxy(local windows account as credential, although has Administrator permission).
It seems a permission problem, but I try a lot and can't solve it.
Would you kind give me some suggestion ?
Thanks a lot.
Tomorrow
I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:
Windows NT user or group '<user or group>' not found. Check the name again.
I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).
But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?
Not sure if this is possible so i had to ask. c",)
I use a windows account "mydomainuser1" to login sql server
The "select current_user" always return "dbo" instead of "mydomainuser1"...
It also return "dbo" after i explicitly run "execute as user=''mydomainuser1" command
Why?
thanks
Hi,
I would like to know is it possible to installing sql 2005 express edition on windows xp with limited account. I tried to install it. After installing, the service can not start. Is there any thing that I misconfigured?
Thanks for reading and answering my post.
We may need to change the account presently used to run the Windows Service "SQL Server Integration Services".What are the implications of making such a change?
View 5 Replies View RelatedI 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 RelatedRan a trace using profiler and found that the CLR is not using the ASPNET windows account to log into SQL 2005, instead using the admin. Some ado.net code does not work properly as a result. Have had to change the connection string to use SQL logins.Spec: Win XP Pro; IIS 5; 2.0.Is this normal? Where security and permissions are concerned, what changes, if any, are there from SQL 2000?
View 5 Replies View Related
Is it Possible adding Windows (activi directry users) from SQL Server which is running Windows authotication.?
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
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?
Does any one come across any difficulty in the following migration?
OS: Wondows Server 2K
DB: SQL Server 2K Standard Edition
Migrating to
OS: Wondows Server 2K3
DB: SQL Server 2K Standard Edition
Thank you,
Gish
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 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 RelatedI 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.
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 Replies View RelatedSqlServer2k 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